DBCC CHECKDB

The command “DBCC CHECKDB(<db name>)” checks the logical and physical integrity of all the objects in the specified database. If corruption has occured for any reason, the DBCC CHECKDB command will find it, and tell you exactly where the problem is. DBCC CHECKDB has a few optional arguments to help you fix your corrupt database.

How do you use it?

Running DBCC CHECKDB is as simple as this:

DBCC CHECKDB(AdventureWorks2008R2)
GO

Default it will output a lot of informational stuff – here showing the last couple of messages:

DBCC results for 'Production.vProductAndDescription'.
There are 1764 rows in 70 pages for object "Production.vProductAndDescription".
DBCC results for 'sys.syscommittab'.
There are 0 rows in 0 pages for object "sys.syscommittab".
DBCC results for 'Sales.SalesTerritoryHistory'.
There are 17 rows in 1 pages for object "Sales.SalesTerritoryHistory".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'AdventureWorks2008R2'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If any corruption is detected, the command will raise an error, and highlight the problem. This is the output from a CHECKDB command on a corrupt database:

Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 983674552, index ID 1, partition ID 72057594058571776, 
alloc unit ID 72057594065977344 (type In-row data). Page (1:91587) is missing 
a reference from previous page (1:111583). Possible chain linkage problem.

Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 983674552, index ID 1, partition ID 72057594058571776, 
alloc unit ID 72057594065977344 (type In-row data). Page (1:111582) was not 
seen in the scan although its parent (1:111431) and previous (1:111581) refer 
to it. Check any previous errors.

The final few lines are:

CHECKDB found 0 allocation errors and 7 consistency errors 
in database 'AdventureWorks2008R2'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (AdventureWorks2008R2).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s