INF: DB Maintenance Wizard Warns About Use on Large Databases (170638)
The information in this article applies to:
This article was previously published under Q170638 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.
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.
Modification Type: | Major | Last Reviewed: | 9/1/2006 |
---|
Keywords: | kberrmsg kbprb KB170638 |
---|
|