SQL Server Maintenance Plans - how I use them

Jonathan Cox (@hackdba) has a good post for starting out on SQL Maintenance Plans.

I like the maintenance plan wizard too, but it doesn't really give good advice about some of the operations.

The critical maintenance pieces are backup and integrity checking. You gotta do both of these. And you need to understand full backups and log backups and database recovery modes (Full & Simple primarily) otherwise you'll end up with a disk full error eventually. The Cleanup task also ensures that you delete your old backup files and don't fill up the backup drive either. I could (and maybe should) write another post about the right kind of SQL backups and recovery models, but lots of people already have. If you're reading this and don't know what I mean, then please google SQL backups and recovery models and educate yourself (before it's too late!).

I don't like to use the maintenance plan for index maintenance, because it's a very large hammer approach to a mostly non-existent problem. Ola Hallengren has a much better approach for index optimization. The default settings for this script are much better choices with regards to choosing when to REORG or REBUILD (and there's no point in doing both).

As far as I'm concerned, updating statistics is optional in most scenarios.

PLEASE PLEASE PLEASE DO NOT shrink databases and/or log files in a maintenance plan.

Mike