Maintenance Plan Wizard

The Maintenance Plan Wizard helps you set up the core maintenance tasks to make sure that your database performs well, is regularly backed up, and is free of inconsistencies. The Maintenance Plan Wizard creates one or more SQL Server Agent jobs that perform these tasks on local servers or on target servers in a multiserver environment. Execution can be at scheduled intervals or on demand.

To create or manage maintenance plans, you must be a member of the sysadmin fixed server role. Note that Object Explorer only displays maintenance plans if the user is a member of the sysadmin fixed server role.

If you want to create or manage maintenance plans in a multiserver environment, additional configuration is required. For more information, see Maintenance Plans and Automating Administration Across an Enterprise.

Maintenance plans can be created to perform the following tasks:

• Reorganize the data on the data and index pages by rebuilding indexes with a new fill factor. Rebuilding indexes with a new fill factor makes sure that database pages contain an equally distributed amount of data and free space. It also enables faster growth in the future. For more information, see Fill Factor.
• Compress data files by removing empty database pages.
• Update index statistics to make sure the query optimizer has current information about the distribution of data values in the tables. This enables the query optimizer to make better judgments about the best way to access data, because it has more information about the data stored in the database. Although index statistics are automatically updated by SQL Server periodically, this option can force the statistics to update immediately.
• Perform internal consistency checks of the data and data pages within the database to make sure that a system or software problem has not damaged data.
• Back up the database and transaction log files. Database and log backups can be retained for a specified period. This lets you create a history of backups to be used if you have to restore the database to a time earlier than the last database backup. You can also perform differential backups.
• Run SQL Server Agent jobs. This can be used to create jobs that perform a variety of actions, and also the maintenance plans to run the jobs.

The results generated by the maintenance tasks can be written as a report to a text file, or written to the maintenance plan tables, sysmaintplan_log and sysmaintplan_logdetail, in msdb. To view the results in the log file viewer, right-click Maintenance Plans, and then click View History.

Maintenance plans only run against databases set to compatibility level 80 or higher. The Maintenance Plan Wizard does not display databases set to compatibility level 70 or lower.

To start the Maintenance Plan Wizard

• How to: Start the Maintenance Plan Wizard (SQL Server Management Studio)

To change maintenance tasks

• How to: Change Maintenance Tasks in the Maintenance Plan Wizard

See Also

Tasks

How to: Create a Maintenance Plan
How to: Create Multiserver Maintenance Plans

Concepts

Maintenance Plans

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
12 December 2006	Changed content: Removed note describing requirement to install SSIS. New content: Added information about support for multiserver maintenance plans in SQL Server 2005 Service Pack 2.
17 July 2006	New content: Added information about maintenance plans only executing on databases set to compatibility level 80 or later.
14 April 2006	New content: Corrected name of sysmaintplan_log_detail in msdb to sysmaintplan_logdetail. Added reference to creating separate maintenance plans for master / target server environments.
5 December 2005	New content: Added requirement to install SSIS.