What is Sql Server Maintenance Plan - Best Practices on Tasks and Scheduling?
I am tasked with devising a maintenance plan for our Sql Server 2005 databases. I know for backups I want to do a daily full database backup and transactional log backups every 15 minutes. My problem comes to figuring out which other tasks I want to do and how often I should do them. So, so far I have this in mind. Correct me if there are any flaws in my thinking or a better way to do this. I remembered reading some time ago (when I set up a similar plan at another job) that some of these tasks don't need to be run on a daily basis or should not be run daily. As to which ones, it escapes me. I could use a little guidance on creating a better maintenance plan that will reduce data loss in a disaster, but won't tax the system when running during peak hours (and also increase the performance).
This is a very common task for all DBAs and the right answer is NOT the same for everyone and for SQL server maintenance plans. A lot of other things, it depends on what you need. Most definitely you don't want to run "Shrink Database" as already suggested. It’s EVIL to performance and the below ref will show you why. It causes disk and as well as index fragmentation and this can lead to performance issues. You are better off by pre-allocating a big size for the data and log files so that auto growth will NOT kick in. I didn't understand your #2. selected tables full backup. Can you elaborate more on this?
Coming to Index reorganize, update statistics, and index rebuilds, you need to be careful on how you do this otherwise you will end up using more resources and also end up with performance issues. When you rebuild indexes the statistics of the indexes are updated with full-scan but if you do update statistics after that, then those will be updated again with a default sample (which depends on several factors, usually 5% of the table when table size > 8 MB) which may lead to performance issues. Depending on the edition you have, you may be able to do online index rebuilds. The right way of doing this activity is to check the amount of fragmentation and depending on that either do index rebuild or index reorganize + update statistics. And also you may want to identify which tables need to update stats more frequently and try to update stats more often.