Database maintenance routines
EDIT: Updated the link to the new version of isp_Backup.
Over the past couple of weeks, I have posted various database maintenance stored procedures. Here's a listing of them:
- isp_Backup - full, differential, and transaction log backups
- isp_DBCC_CHECKDB - perform integrity checks on all databases
- isp_ALTER_INDEX - defragments and rebuilds indexes
- isp_UPDATE_STATISTICS - performs update statistics on each table in the database
- isp_Restore - restore newest BAK file in specified directory
Do not put these in the master database. An Admin database should be created where non-application specific objects are put. All of the stored procedures are designed so that they do not need to be in any particular database.
Use them at your own risk. If you don't use them, at the very least create your own. Don't use the maintenance plan wizards or the sqlmaint utility!
Legacy Comments
|
Justin Pitts
2004-07-02 |
re: Database maintenance routines " Don't use the maintenance plan wizards or the sqlmaint utility!" Why? |
|
Tara
2004-07-02 |
re: Database maintenance routines First, what's their to learn when you use wizards? You aren't very close to the process. Second, have you ever tried to figure out why one was failing or not working as desired and it didn't give enough information in the SQL Server Error Log, in the job history, or in the maintnenace plan history? We often get questions on SQLTeam.com about a plan that isn't failing but also isn't working properly. I'd say for developers that aren't DBAs that the wizards are a start in setting up the DBA routines. But there is more to DBA routines than what is available in the maintenance plans. And there's also stuff that shouldn't be turned on in there. Besides, would you create a web site using a wizard? It's much more flexible when you create it on your own. |
|
Derrick Leggett
2004-07-02 |
re: Database maintenance routines I think you can sum it up by saying this. The Maintenance Plans SUCK!!! They were provided so you could have maintenance on your database if you didn't know anything about how SQL Server works. They have zero error reporting. They SUCK!!! Yep, that about sums it up. They SUCK!!! |
|
Greg
2004-07-02 |
re: Database maintenance routines Derrick, don't hold back... how do you really feel? lol |
|
Brett
2004-07-06 |
re: Database maintenance routines Derrick, Really, you shouldn't be so blase... You should really try to be a little bit more passionate about things.... |
|
Lee Dise
2004-07-29 |
re: Database maintenance routines > The Maintenance Plans SUCK!!! Sam Kinnison didn't die, he changed his name and became a DBA! :-) |
|
kristen
2004-08-15 |
re: Database maintenance routines Tara, dunno if you just want to change the URLs on these to point to your newer versions? (Don't suppose it matters, you've edited the old ones to indicate there are new ones - but you see I was just doing a TEST!) K |
|
Tara
2004-08-15 |
re: Database maintenance routines Once I get all the updated versions out there, I'm going to create a new list in a blog. |
|
Tara
2004-09-23 |
re: Database maintenance routines I decided to just edit this one. Updates complete. |
|
Arundhathi
2005-06-09 |
DBA routines What kind of routines would you suggest to restrict access on specific tables to users as a DBA |
|
Tara
2005-06-10 |
re: Database maintenance routines You'll need to use the GRANT function to provide access. |
|
Chintan
2005-10-25 |
re: Database maintenance routines how do i edit a Database Maintenance Plan using the wizard please reply to my id asap : chintan_gandhi@infosys.com |
|
Ken
2006-07-18 |
re: Database maintenance routines I'm currently training on my Windows XP Pro machine running SQL Server 200 PE SP4. When I try to execute your maintenance stored procedures, I'm getting error with reference to SYSNAME: Server: Msg 2715, Level 16, State 3, Procedure isp_Backup_LS, Line 0 Column or parameter #-4: Cannot find data type SYSNAME. Server: Msg 2715, Level 16, State 1, Procedure isp_Backup_LS, Line 0 Column or parameter #-5: Cannot find data type SYSNAME. Parameter '@DBName' has an invalid data type. Parameter '@cmd' has an invalid data type. All other variables appear to be declared without error. Newbie ... Ken |
|
Tara
2006-07-18 |
re: Database maintenance routines Use nvarchar(256) instead of sysname then. |
|
Manou
2006-08-15 |
re: Database maintenance routines Hi, I'm a web developer trying to be a DBA! If I shouldn't use a maintainence plan, what should I be doing? Thanks in advance.. |
|
Tara
2006-08-15 |
re: Database maintenance routines Manou, I provided my alternative to the maintenance plans in this blog post. Check out the stored procedures. |
|
place without abuse
2006-10-25 |
place without abuse I haven't gotten anything done , but whatever. Today was a loss. So it goes. I've just been sitting around not getting anything done. I can't be bothered with anything these days, but I don't care. |
|
Anwar
2006-11-08 |
re: Database maintenance routines Hi tara, I am thinking of using the isp_Backup_TLog Script to run on the weekend to backup the logs. Will this truncate all the logs. The recovery Model is set to simple. Regards, |
|
Tara
2006-11-08 |
re: Database maintenance routines Anwar, You can't backup the transaction log of a database in SIMPLE recovery model. |