Commonly used DBCC validation statements and repair options

Article ID: 867936 - View products that this article applies to.
Expand all | Collapse all

 



SUMMARY

This article contains information regarding some commonly used SQL Server DBCC (Database Consistency Checker) validation statements and repair options that are run through Query Analyzer.

Note - The majority of the below information was taken directly from SQL Books Online.

MORE INFORMATION

DBCC Validation Statements


Common DBCC validation statements include DBCC CHECKDB, DBCC CHECKTABLE, and DBCC CHECKALLOC.

Note - You can run Database Consistency Checker statements in SQL Server Query Analyzer. To simplify the process, you should be connected to the database which you are validating and repairing (if a repair is necessary).

>>DBCC CHECKDB checks the allocation and structural integrity of all the objects in the specified database. It checks the linkages and sizes of text, ntext, and image pages for each table, and the allocation of all pages in the database.

For each table in the database, DBCC CHECKDB checks that:

1. Index and data pages are correctly linked.
2. Indexes are in their proper sort order.
3. Pointers are consistent.
4. The data on each page is reasonable.
5. Page offsets are reasonable.

If you are connected to the database that you wish to validate, the statement would simply be DBCC CHECKDB. If you are connected to a different database, the database name must be included. For example, if you need to check the SIVApplication database and you are connected to the Master database, the statement would be:

DBCC CHECKDB ('SIVApplication')

Note - Errors indicate potential problems in the database and should be corrected immediately.

>>DBCC CHECKTABLE performs a physical consistency check on tables and indexed views. It checks the integrity of the data, index, text, ntext, and image pages for the specified table or indexed view.

Note - To perform DBCC CHECKTABLE on every table in the database, use DBCC CHECKDB.

For the specified table, DBCC CHECKTABLE checks that:

1. Index and data pages are correctly linked.
2. Indexes are in their proper sort order.
3. Pointers are consistent.
4. The data on each page is reasonable.
5. Page offsets are reasonable.

The table name must be specified in the statement.
For example, if you need to check the GLTran table on the current database, the statement would be:

DBCC CHECKTABLE ('GLTran')

Note - Errors indicate potential problems in the table and should be corrected immediately.

>>DBCC CHECKALLOC checks the consistency of disk space allocation structures for a specified database. It checks allocation and page usage in a database, including indexed views.

If you are connected to the database whose allocation you need to validate, the statement would simply be DBCC CHECKALLOC. If you are connected to a different database, the database name must be included. For example, if you need to check the allocation of the SIVApplication database and you are connected to the Master database, the statement would be:

DBCC CHECKALLOC ('SIVApplication')

Note - It is not necessary to execute DBCC CHECKALLOC if DBCC CHECKDB has already been executed. DBCC CHECKDB is a supercheck of DBCC CHECKALLOC and includes allocation checks in addition to checks of index structure and data integrity.

DBCC Repair Options

If errors are found, DBCC provides three repair options: REPAIR_FAST, REPAIR_REBUILD, and REPAIR_ALLOW_DATA_LOSS.

Note - Prior to running ANY repair option, make a BACKUP of the database which can be restored in case an undesired data loss occurs.

>>REPAIR_FAST performs minor, nontime-consuming repair actions such as repairing extra keys in nonclustered indexes. These repairs can be done quickly and without risk of data loss.

>>REPAIR_REBUILD performs all repairs done by REPAIR_FAST and includes time-consuming repairs such as rebuilding indexes. These repairs can be done without risk of data loss.

>>REPAIR_ALLOW_DATA_LOSS performs all repairs done by REPAIR_REBUILD and includes allocation and deallocation of rows and pages for correcting allocation errors, structural row or page errors, and deletion of corrupted text objects. These repairs can result in some data loss. The repair may be done under a user transaction to allow the user to roll back the changes made. If repairs are rolled back, the database will still contain errors and should be restored from a backup. If a repair for an error has been skipped due to the provided repair level, any repairs that depend on the repair are also skipped. After repairs are completed, back up the database.

Notes

1. The correct way to run a DBCC CHECKDB or DBCC CHECKTABLE statement with valid REPAIR options is to start SQL Server normally and then to explicitly set the database in single user mode. You can do this either from the Enterprise Manager or from the Query Analyzer.

From Enterprise Manager:
a. Right-click the database name, and then click Properties.
b. In the Properties dialog box, click Options.
c. Select the single user option, and then click OK.

From Query Analyzer:
Use master

go
sp_dboption dbname, single, true

After the database is in single user mode, you can then run the DBCC CHECKDB or DBCC CHECKTABLE statements with the valid REPAIR options.

2. To repair a single table, DBCC CHECKTABLE can be used with the repair option.

For example, if you need to run REPAIR_REBUILD on the Batch table, the statement would be:
DBCC CHECKTABLE ('Batch', REPAIR_REBUILD)


3. To repair the entire database or to repair allocation errors, DBCC CHECKDB should be used with the repair option. DBCC CHECKDB is the safest repair statement because it identifies and repairs the widest possible range of errors. If only allocation errors are reported for a database, you may execute DBCC CHECKALLOC with a repair option to correct them. However, to ensure that all errors (including allocation errors) are repaired properly, execute DBCC CHECKDB with a repair option.

For example:

- If DBCC CHECKDB reports only allocation errors on the SIVApplication database and you need to run REPAIR_FAST, but you do not want to run a repair on the entire database, run the following statement:

DBCC CHECKALLOC ('SIVApplication', REPAIR_FAST)


- If you need to run REPAIR_REBUILD on the entire SIVApplication database, run the following statement:

DBCC CHECKDB ('SIVApplication', REPAIR_REBUILD)


REFERENCES

For more information regarding any DBCC validation statement and repair option, refer to SQL Server Books Online.

Properties

Article ID: 867936 - Last Review: July 21, 2011 - Revision: 3.0
APPLIES TO
  • Microsoft Dynamics SL 2011
  • Microsoft Dynamics SL 7.0
  • Microsoft Dynamics SL 6.5
Keywords: 
kbinfo kbexpertiseadvanced kbmbsmigrate KB867936

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com