Using the Database Maintenance Plan Wizard

The screens in the Database Maintenance Plan Wizard are described below. On most of the screens, the best options will already be selected for you. Unless you have a clear reason not to, accept the defaults.

1. Introduction screen

   Provides a brief explanation of the wizard. Select the database to set up a maintenance plan for.

2. About the Data in Your Database screen

   Tailoring the maintenance plan to the selected database requires that you know how often the data is updated and created.

   Updated data is existing data in the database that is modified. Calculate the amount of data that is updated daily and match that amount to the closest value in the following list:

   • Data is never changed
   • Less than 5%
   • More than 5% but less than 20%
   • More than 20%

   For example, if you have a 22MB database and 5MB of data in that database is updated each day, choose the 20% option.

   Created data, referred to as Data Growth on the screen, is data that is new to the database and causes new records to be stored. Calculate the amount of data that is added daily and match that amount to the closest value in the following list:

   • No new data is ever added
   • Less than 2%
   • More than 2% but less than 10%
   • More than 10%

   For example, if you have a 32MB database that is growing slowly, you will choose the Less Than 2% option.

3. Data Verification screen

   Verifying data is the process of checking that correct relationships among data, metadata, and allocations are maintained. The following are tests you can run:

   Check data linkage

   Examines each data page of each table in the database to verify that they are joined in the correct order. Pages that are not joined correctly will cause errors when queries access broken pages.

   For more information about data linkage, see DBCC CHECKDB in the Microsoft SQL Server Transact-SQL Reference.

   Check index linkage

   Examines each index page of each table in the database to verify that they are joined in the correct order. Pages that are not joined correctly will cause errors when queries access the broken pages.

   For more information about index linkage, see DBCC CHECKDB in the Microsoft SQL Server Transact-SQL Reference.

   Check data allocation

   Examines each data page of each table in the database to verify that the server's internal information about which pages are in use and which pages are not in use is correct. Mismatches will cause errors when queries access these pages. In the worst case, data can be lost.

   For more information about data allocation, see DBCC NEWALLOC in the Microsoft SQL Server Transact-SQL Reference.

   Check index allocation

   Examines each index page of each table in the database to verify that the server's internal information about which pages are in use and which pages are not in use is correct. In the worst case, data can be lost.

   For more information about index allocation, see DBCC NEWALLOC in the Microsoft SQL Server Transact-SQL Reference.

   Check text/image data allocation

   Runs allocation and linkage checks for each text/image page in the database.

   For more information, see DBCC TEXTALL with the FAST option in the Microsoft SQL Server Transact-SQL Reference.

   Check system data

   Examines the system tables (for example, sysindexes and sysobjects) to verify that the information in and between them is consistent.

   For more information, see DBCC CHECKCATALOG in the Microsoft SQL Server Transact-SQL Reference.

   Add the data verification tests to

   Determines the schedule for verification tests. For daily data verification, choose the daily maintenance plan. For weekly data verification, choose the weekly maintenance plan.

4. Data Optimization screen

   When you delete, update, and create data records, the fullness of SQL Server's pages can become nonuniform over time. Data optimization is the process of refilling pages to a uniform fullness and of updating SQL Server metadata concerning table data distribution. Effective data optimization will help to maximize SQL Server performance.

   Choose from the following options to optimize your data:

   Update optimizer information

   Specifies that each data page of each table in the selected database will be examined to gather and store an accurate sampling (per table) of that data. This distribution information will subsequently be used by the server when optimizing queries.

   For more information, see the UPDATE STATISTICS statement in the Microsoft SQL Server Transact-SQL Reference.

   Reorganize data and index pages

   Specifies that all indexes on all tables in the database should be rebuilt. This option is useful for pages that become full and then split. The splitting process increases the size of a table and, because it happens dynamically, can increase the execution time of queries that modify data. Rebuilding the indexes refills each page to its optimal fullness based on the value that is specified in the Change Free Space To box.

   For more information, see DBCC DBREINDEX in the Microsoft SQL Server Transact-SQL Reference.

   Maintain original free space

   Indicates that the percentage of free space specified when the index was created (or last rebuilt using DBCC DBREINDEX) will be used again to rebuild the index. Free space is the inverse of fill factor (for example, free space of 30 percent is the same as a fill factor of 70 percent).

   For more information, see DBCC DBREINDEX in the Microsoft SQL Server Transact-SQL Reference.

   Change free space to

   Specifies the percentage of free space to leave in each index leaf page when the index is rebuilt. It is important to have enough free space to accommodate future growth and to reduce page splitting. Free space is the inverse of fill factor (for example, free space of 30 percent is the same as a fill factor of 70 percent).

   For more information, see the FILLFACTOR clause of the CREATE INDEX statement in the Microsoft SQL Server Transact-SQL Reference.

   Add the Data Optimizations to

   Specifies that data optimization options be added to the daily or weekly maintenance plan.

5. Data Protection screen

   Protecting your data is crucial to protecting the integrity of a database. With the Data Protection option, you can schedule database backups. Maintaining backups provides you a way to recover data in case of equipment or other problems.

   Good candidates for daily backups are:

   • Databases with data that is frequently modified and/or added
   • Databases that are critical to operations
   • Databases that are under heavy loads and/or experience frequent equipment failures

   Good candidates for weekly backups are:

   • Databases with data that is changed or added infrequently
   • Databases with data that is easily recovered from other sources

   Select the Only Do The Backup If check box if you want to do a backup only if data verification tests succeed. This assures you that backups will always contain verified data.

6. Backup Destination screen

   Specifies a destination for the database backup. A backup can be saved to disk or tape.

   Use the Delete Files Older Than check box to delete backup files older than 1 week.

7. When to Run, Who to Notify screen

   Specifies a schedule for a backup, and whether to generate a report and send a notice through e-mail that the backup is completed.

8. Wizard Complete screen

   Confirms the selections you made for the maintenance plan.

9. Print dialog

   Displays a message that prompts you to print your maintenance plan, if you have not already selected to do so.

   Important After you create your maintenance plan and exit the wizard, you cannot easily access the maintenance plan. It is strongly recommended that you print your database maintenance plan.

10. Confirmation dialog

    Displays a message that states that the wizard is complete. Presents the option to run the plan immediately. If you run the plan immediately, it will not affect the schedule you have specified.