SQL Server 2008 Table Maintenance - Rebuild, Reorganize, Update Stats, Check Integrity etc HELP!

Question

I'm migrating a ~15GB database from SQL Server 2005 to a new server running SQL Server 2008, and along with that I need to create all the new Maintenance Plans. I can take care of all the backup stuff, but the table maintenance baffles me some. Does anyone have any input on how often I should (or how often you do would suffice too) the following tasks?

• Check Database Integrity
• Rebuild Indexes
• Reorganize Indexes
• Update Statistics
• Shrink Database?

Am I missing anything? Again if you can share how often you do these tasks that would be great...and/or share any general information about your approach to table maintenance that would be helpful. Lastly does it matter what order I run these tasks in (when setting up a job)?

Oh and I'm open to any links which might be of help!

Answer 1

Usually you'd do weekly or daily depending on your usage and maintenance windows.

You pretty much never shrink, especially not scheduled.

For rebuild/reorganise and statistics there are scripts (such as SQL Fool's one) that do a better job. Note: reorganise and rebuild are not exclusive but work different ways. 3rd party scripts can choose the best option (based on fragmentation).

We use weekly index/DBCC and daily statistics. And never shrink.

From Simple-talk: Don't Forget to Maintain Your Indexes

Edit: The URL is has ’ that messes up http://www.simple-talk.com/sql/database-administration/don’t-forget-to-maintain-your-indexes/