INF: DB Maintenance Wizard Warns About Use on Large Databases

ID: Q170638

The information in this article applies to:

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:

Q140569 : 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

Last Reviewed: April 15, 1999