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

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

Re: Shrinking. I see so many people getting their claws out at the very mention of 'shrink' and 'database' in the same sentence, so time to clear things up a little.

Shrinking data is baaaaad. It literally turns your indexes into quivering shells of their former glory. Don't do it.

Shrinking log should not be done routinely, but if you have a ridiculously outsized log (i.e. in one case I saw a 40GB log for a 100MB database, due to whoever set it up putting recovery model to full then never dumping the transaction log), you can shrink the log (after BACKUP LOG) to reclaim that space without any ill effects (although, obviously, the SHRINK will chew up I/O while it's running).

PS: Speaking of log files, check your log file size and autogrowth settings. Small autogrowth settings can lead to underperforming log I/O (due to a poorly-explained feature in SQL Server called Virtual Log Files), particularly on batch transactions.

For scripts, Ola Hallengren has wicked awesome things maintenance. Use maintenance plans for now, once you become a little comfortable with the process of doing maintenance, switch to using scripts, running scheduled SQL agent jobs.

At my place of work, I am doing DBCC, reorg'ing indexes every night. On the weekends, it's DBCC, rebuild indexes, update stats.

Don't shrink anything. If you do, Brent Ozar will yell at you, it is never fun to have a SQL MCM yell at you.