INF: DB Maintenance Wizard Warns About Use on Large Databases

Last reviewed: July 21, 1997
Article ID: Q170638

The information in this article applies to:

Microsoft SQL Server, version 6.5

SUMMARY

You will receive the following warning message if you use the Database Maintenance Plan Wizard on databases that are larger than 400 MB:

   Database 'test' is over 400 MB in size. It is not recommended that you
   run the Database Maintenance Plan Wizard on databases exceeding this
   size. Do you still want to continue?

There are no problems with the Database Maintenance Plan Wizard working on databases exceeding this size. The message is intended to caution you that these tasks may be very resource and time-intensive operations, and to consider the implications of these operations relative to the available maintenance window.

MORE INFORMATION

You can use the Database Maintenance Plan Wizard to automate routine maintenance tasks such as rebuilding indexes, updating statistics, and performing backups and consistency checks on databases. On large databases, these maintenance operations may take an extended period of time to perform. Therefore, you should give some thought to the amount of time and resources required to perform these tasks, and whether the operations can reasonably be performed in the normal maintenance window allowed for the server.

For example, running a full database consistency check (DBCC CHECKDB and DBCC NEWALLOC) may take anywhere from a few minutes to several days, depending on the size of the database, the I/O capacity of the system, and the complexity of the constraints and indexes. Thought must be given to the timing of these operations and their impact on the system. For more detail on optimizing the performance if these consistency checks, see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q140569
   TITLE     : INF: How to Improve DBCC Performance on SQL Server

If backups are scheduled and the dumps are held on disk for several weeks before being purged, you must also consider disk space requirements. For example, if a 1.5 GB database is backed up and the backups are kept for four weeks, an additional 6 GB of disk space would be required on the system. Rather than using the Database Maintenance Plan Wizard to purge the files, you may want to consider archiving the backup to tape with your normal system backup, and deleting the file yourself after the system backup.

When you use the Database Maintenance Plan Wizard to schedule maintenance operations on a larger database, consider performing these types of operations manually and noting the length of time taken to perform each option individually before scheduling the operations with the Database Maintenance Plan Wizard. The Data Verification prompts given in the wizard correspond to running DBCC CHECKDB, DBCC NEWALLOC, DBCC TEXTALL, and DBCC CHECKCATALOG, respectively. The DBCC CHECKDB and DBCC NEWALLOC are run with the NOINDEX option unless you indicate that indexes should be checked. Consult the Transact-SQL documentation under "DBCC Statement" for more details about the nature of the checks performed by each of these operations.

Additional query words: sqlmaint sql65 msg indices
Keywords : kberrmsg SSrvAdmin SSrvEntMan
Version : 6.5
Platform : WINDOWS
Issue type : kbprb
Resolution Type : Info_Provided

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: July 21, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.