April 10, 2009 at 1:07 pm
Are there best practices for creating maintenance plans in SQL Server 2000? Anything from Microsoft? I've searched high and low and can't seem to find any official (my boss will want a reputable source) best practices for creating maintenance plans. For example, I would think you'd have separate maintenance plans for the system and user databases. I would also think you would check database integrity and optimaize the user databases frequently (maybe once a week?). Any help from you gurus out there would be great - thanks in advance!
Bill
April 15, 2009 at 5:30 am
I would think that the answer to your question will be very different depending on who you ask.
My personally view is that as a best pratice you shouldn't use maintenance plans at all. The problem with MP is that you can't finetune them. For example a reindex task will reindex all indexes in all tables whether or not that is necessary. With large database this will cause big problems because the transaction log will need lot's of space and the job can take up to several days.
Small databases won't take that long, but usually reindexing on small database has much less impact.
You can use MP's for backups, but even for this task I prefer my own maintenance scripts. I've seen too many MP's fail because they tried to take log backups but the database was in simple mode or backup database which are offline.
If you don't feel comfortable writing your own scripts, look in the scripts section of this site and you will find plenty.
[font="Verdana"]Markus Bohse[/font]
April 15, 2009 at 7:12 am
Thanks Marcus, I appreciate the insights
April 17, 2009 at 12:25 pm
I focus on these areas:
Physical File Fragmentation
Database and Log File Management
tempdb Maintenance
msdb Maintenance
Index Maintenance
Statistics Maintenance
Data Corruption Detection
Database and Log File Protection
Database Maintenance Monitoring
I have a Powerpoint presentation on the above. If you want a copy, e-mail me at bradmcgehee@hotmail.com.
Also, see my recent blog post at: http://www.sqlservercentral.com/blogs/aloha_dba/default.aspx.
Brad M. McGehee
DBA
December 14, 2009 at 11:51 am
You can download the presentation I referred to in my previous post at: http://www.bradmcgehee.com/presentations. It is located near the bottom of the webpage.
Brad M. McGehee
DBA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply