Article ID: 2015748 - View products that this article applies to.
When the DBCC CHECKDB (or other similar commands like CHECKTABLE) is executed, a message like the following is written to the SQL Server ERRORLOG:
2010-03-31 22:07:06.34 spid53 DBCC CHECKDB (mydb) executed by MYDOMAIN\theuser found 15 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds. Internal database snapshot has split point LSN = 00000026:0000089d:0001 and first LSN = 00000026:0000089c:0001. This is an informational message only. No user action is required.
This message shows how many database consistency errors were found and how many were repaired (if a repair option was used with the command). This message is also written to the Windows Application Event Log as an Information Level message with EventID=8957 (even if errors are reported this message is an Information Level Message).
The information in the message starting with "internal database snapshot..." only appears if DBCC CHECKDB was run online, which is if the database is not in SINGLE_USER mode. This is because for an online DBCC CHECKDB, an internal database snapshot is used to present a consistent set of data to check.
This article will not discuss how to troubleshoot each specific error reported by DBCC CHECKDB but rather the general approach if errors are reported. Any reference to CHECKDB in this article also applies to DBCC CHECKTABLE and CHECKFILEGROUP unless specifically stated.
DBCC CHECKDB checks the physical and logical consistency of database pages, rows, allocation pages, index relationships, systerm table referential integrity, and other structure checks. If any of these checks fail (depending on the options you have chosen), errors will be reported as part of the command.
The cause of these problems can vary from file system corruption, underlying hardware system issues, driver issues, corrupted pages in memory, or problems with the SQL Server Engine. Read through the Resolution section for more information on how to find the cause of errors that are reported.
The first, best solution if DBCC CHECKDB reports consistency errors is to restore from a known good backup. However, if you cannot restore from a backup, then CHECKDB provides a feature to repair errors. If system level problems such as the file system or hardware may be causing these problems, it is recommended you correct these first before restoring or running repair.
When you run DBCC CHECKDB a recommendation is provided to indicate what the minimum repair option that is required to repair all errors. These messages may look something like the following:
CHECKDB found 0 allocation errors and 15 consistency errors in database 'mydb'.
The repair recommendation is the minimum level of repair to attempt to resolve all errors from CHECKDB. This does not mean that this repair option will actually fix all errors. Furthermore, not all errors reported may require this level of repair to resolve the error. This means that not all errors reported by CHECKDB when repair_allow_data_loss is recommended will result in data loss. Repair must be run to determine if the resolution to an error will result in data loss. One technique to help narrow down what the repair level will be for each table is to use DBCC CHECKTABLE for any table reporting an error. This will show what the minimum level of repair for a given table.
To find the cause of why database consistency errors have occurred, consider these methods:
For details on the syntax of DBCC CHECKDB and information/options about how to execute the command, read the SQL Server Books Online topic on the DBCC CHECKDB command.
If any errors were found by CHECKDB, additional messages like the following are reported in the ERRORLOG for the purposes of Error Reporting:
2010-03-31 22:07:06.34 spid53 Using 'dbghelp.dll' version '4.0.5'
The files used for error reporting include a SQLDump<nnn>.txt file. This file can be useful for historical purposes as it contains a list of the errors found from CHECKDB in an XML format.
To find out when the last time DBCC CHECKDB was run without errors detected for a database (the last known clean CHECKDB), check the SQL Server ERRORLOG for a message like the following for your database or system database (this message is written as an Information Level message in the Windows Application Event Log with EventID = 17573):
2010-04-01 10:13:59.80 spid7s CHECKDB for database 'master' finished without errors on 2010-03-31 22:11:11.417 (local time). This is an informational message only; no user action is required
(http://go.microsoft.com/fwlink/?LinkId=151500)for other considerations.
Article ID: 2015748 - Last Review: April 1, 2010 - Revision: 1.0