Brad’s Sure Guide to SQL Server Maintenance Plans- P12: SQL Server has a reputation as being a simple database application to install, configure, and maintain. This is a little misleading. SQL Server is a powerful relational database that can handle the needs of the largest organizations and, as such, its proper maintenance almost certainly requires the attention of an experienced DBA. | Chapter 3 Getting Started with the Maintenance Plan Wizard Reorganize Index The Reorganize Index task runs the ALTER INDEX statement with the REORGANIZE option on the indexes in the selected databases. This task helps to remove index fragmentation but does not update index and column statistics. If you use this option to remove index fragmentation then you will also need to run the Update Statistics task as part of the same Maintenance Plan. In addition you won t need to run the Rebuild Index task as the use of Reorganize Index task followed by the Update Statistics task renders redundant the Rebuild Index task. Update Statistics The Update Statistics task runs the sp_updatestats system stored procedure against the tables of the selected databases updating index and column statistics. It is normally run after the Reorganize Index task is run. Don t run it after running the Rebuild Index task as the Rebuild Index task performs this same task automatically. Execute SQL Server Agent Job The Execute SQL Server Agent Job task allows you to select SQL Server Agent jobs ones you have previously created and to execute them as part of a Maintenance Plan. This feature offers you additional flexibility when performing database maintenance using the Maintenance Plan Wizard. History Cleanup The History Cleanup task deletes historical data from the msdb database including historical data regarding backup and restore SQL Server Agent and Maintenance Plans. If you don t perform this task periodically then over time the msdb database can grow very large. Back Up Database Full The Back Up Database Full task executes the BACKUP DATABASE statement and creates a full backup of the database. You will probably want to run this task daily against your system and production databases. In most cases the databases you will be backing up with this task use the Full Recovery model and you will also want to run the Backup Database Transaction Log task as part of your Maintenance Plan. 56 .
