Checking and Repairing Database Integrity

DBCC CHECKDB is the most frequently used validation command for checking the logical and physical integrity of the whole database. Essentially, DBCC CHECKDB is a superset command that actually runs CHECKALLOC, CHECKTABLE, and CHECKCATALOG.

The following are some recommendations for using DBCC CHECKDB:

• Administrators should run DBCC CHECKDB rather than the individual operations because it identifies most of the errors and is generally safe to run in a production environment.

• After running DBCC CHECKDB, administrators should run it again with the REPAIR argument to repair reported errors.

• DBCC CHECKDB can be time-consuming and it performs schema locks that prevent metadata changes; therefore, it is highly recommended that administrators run it during nonproduction hours.

• The command should run on a table-by-table basis if it is used to perform consistency checks on large databases.