I recently wrote an article about maintenance plans for SQL Server Magazine but it hasn't come out yet. I am starting my vacation in two hours so just very briefly:

- When you run the wizard, stick to the defaults
- I usually create one plan for system databases and one or more plans for everything else
- Optimizations: I do the "Reorganize data and index pages" option once a week (leave 10% space), no shrinking
- Integrity checks: once a week, not before backups
- Full Backup: Once a day, keep the last 5 days
- Log backups: depends on the server, at least twice a day, keep the last 5 days
- Reporting: it's really up to you. If there's more than one SQL Server on the network, I redirect history to one central server, then I only have to check logs in one place
- Each maintenance option creates a scheduled job, I sometimes modify the job definition to email me if the job fails. You can set up the plan to email you the status but it ends up being too much mail, I only want to notified for certain jobs and only when they fail.
- Some KB issues you should know about:
--- Q303292 “Expired Transaction Log Backups May Not Be Deleted by Maintenance Plan” (http://support.microsoft.com/default.aspx?scid=kb;en-us;303292)
--- Q242500 “Sqlmaint does not report error on BACKUP LOG when Truncate Log on Checkpoint is set” (http://support.microsoft.com/default.aspx?scid=kb;EN-US;242500)
--- Q264194 “DB Maintenance Plan Cannot Be Modified to Include/Exclude Integrity Checks Before Backups” (http://support.microsoft.com/default.aspx?scid=kb;en-us;264194)

Roman

>What things should be done in a good maintenance plan for a database? Basically I'm looking for a list of the "must" do in the maintenance plan and a list of "optional" things to do for a maintenance plan to run either daily, weekly, or monthly.
>
>Thanks.