Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/170638

From BetaArchive Wiki
Knowledge Base


INF: DB Maintenance Wizard Warns About Use on Large Databases

Article ID: 170638

Article Last Modified on 9/1/2006



APPLIES TO

  • Microsoft SQL Server 6.5 Standard Edition



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.


Additional query words: sqlmaint sql65 msg indices

Keywords: kberrmsg kbprb KB170638