Database Maintenance Plan Wizard

The Database Maintenance Plan Wizard can be used to help you set up the core maintenance tasks necessary to ensure that your database performs well, is regularly backed up in case of system failure, and is checked for inconsistencies. The Database Maintenance Plan Wizard creates a Microsoft® SQL Server™ 2000 job that performs these maintenance tasks automatically at scheduled intervals.

The maintenance tasks that can be scheduled to run automatically are:

• Reorganizing the data on the data and index pages by rebuilding indexes with a new fill factor. This ensures that database pages contain an equally distributed amount of data and free space, which allows future growth to be faster. For more information, see Fill Factor.
  
  
• Compressing data files by removing empty database pages.
  
  
• Updating index statistics to ensure the query optimizer has up-to-date information about the distribution of data values in the tables. This allows 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 be updated immediately.
  
  
• Performing internal consistency checks of the data and data pages within the database to ensure that a system or software problem has not damaged data.
  
  
• Backing up the database and transaction log files. Database and log backups can be retained for a specified period. This allows you to create a history of backups to be used in the event that you need to restore the database to a time earlier than the last database backup.
  
  
• Setting up log shipping. Log shipping allows the transaction logs from one database (the source) to be constantly fed to another database (the destination). Keeping the destination database in synchronization with the source database allows you to have a standby server, and also provides a way to offload query processing from the main computer (source server) to read-only destination servers.

The results generated by the maintenance tasks can be written as a report to a text file, HTML file, or the sysdbmaintplan_history tables in the msdb database. The report can also be e-mailed to an operator.

To start the Database Maintenance Plan Wizard