DBCC CHECKDB (Transact-SQL)
Checks the logical and physical integrity of all the objects in the specified database by performing the following operations:
- Runs DBCC CHECKALLOC on the database.
- Runs DBCC CHECKTABLE on every table and view in the database.
- Runs DBCC CHECKCATALOG on the database.
- Validates the contents of every indexed view in the database.
- Validates the Service Broker data in the database.
This means that the DBCC CHECKALLOC, DBCC CHECKTABLE, or DBCC CHECKCATALOG commands do not have to be run separately from DBCC CHECKDB. For more detailed information about the checks that these commands perform, see the descriptions of these commands.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
DBCC CHECKDB
[
[ ( database_name | database_id | 0
[ , NOINDEX
| , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]
) ]
[ WITH
{
[ ALL_ERRORMSGS ]
[ , NO_INFOMSGS ]
[ , TABLOCK ]
[ , ESTIMATEONLY ]
[ , { PHYSICAL_ONLY | DATA_PURITY } ]
}
]
]
Arguments
- database_name | database_id | 0
Is the name or ID of the database for which to run integrity checks. If not specified, or if 0 is specified, the current database is used. Database names must comply with the rules for identifiers.
- NOINDEX
Specifies that intensive checks of nonclustered indexes for user tables should not be performed. This decreases the overall execution time. NOINDEX does not affect system tables because integrity checks are always performed on system table indexes.
REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
Specifies that DBCC CHECKDB repair the found errors. The specified database**must be in single-user mode to use one of the following repair options.- REPAIR_ALLOW_DATA_LOSS
Tries to repair all reported errors. These repairs can cause some data loss.
- REPAIR_FAST
Maintains syntax for backward compatibility only. No repair actions are performed.
- REPAIR_REBUILD
Performs both minor, quick repairs, such as repairing extra keys in nonclustered indexes, and time-consuming repairs such as rebuilding indexes. These repairs can be performed without risk of data loss.
Important
Use the REPAIR options only as a last resort. To repair errors, we recommend restoring from a backup. Repair operations do not consider any of the constraints that may exist on or between tables. If the specified table is involved in one or more constraints, we recommend running DBCC CHECKCONSTRAINTS after a repair operation. If you must use REPAIR, run DBCC CHECKDB without a repair option to find the repair level to use. If you use the REPAIR_ALLOW_DATA_LOSS level, we recommend that you back up the database before you run DBCC CHECKDB with this option.
- REPAIR_ALLOW_DATA_LOSS
ALL_ERRORMSGS
Displays all reported errors per object. In SQL Server 2005 Service Pack 3 (SP3), all error messages are displayed by default. Specifying or omitting this option has no effect. In earlier versions of SQL Server, only the first 200 error messages for each object are displayed if ALL_ERRORMSGS is not specified. Error messages are sorted by object ID, except for those messages generated from tempdb database.In SQL Server Management Studio, the maximum number of error messages returned is 1000. Using Management Studio, you may need to run DBCC CHECKDB multiple times to get a complete list of errors when ALL_ERRORMSGS is specified. We recommend that you run the DBCC command by using the sqlcmd utility or by scheduling a SQL Server Agent job to run the command and direct the output to a file. Either of these methods will ensure that running the command once will report all error messages.
- NO_INFOMSGS
Suppresses all informational messages.
TABLOCK
Causes DBCC CHECKDB to obtain locks instead of using an internal database snapshot. This includes a short-term exclusive (X) lock on the database. TABLOCK will cause DBCC CHECKDB to run faster on a database under heavy load, but decreases the concurrency available on the database while DBCC CHECKDB is running. For more information about locks, see Lock Modes.TABLOCK limits the checks that are performed; DBCC CHECKCATALOG is not run on the database, and Service Broker data is not validated.
- ESTIMATEONLY
Displays the estimated amount of tempdb space that is required to run DBCC CHECKDB with all the other specified options. The actual database check is not performed.
- PHYSICAL_ONLY
Limits the checking to the integrity of the physical structure of the page and record headers, the physical structure of B-trees, and the allocation consistency of the database. This check is designed to provide a small overhead check of the physical consistency of the database, but it can also detect torn pages, checksum failures, and common hardware failures that can compromise a user's data. PHYSICAL_ONLY always implies NO_INFOMSGS and is not allowed with any one of the repair options.
DATA_PURITY
Causes DBCC CHECKDB to check the database for column values that are not valid or out-of-range. For example, DBCC CHECKDB detects columns with date and time values that are larger than or less than the acceptable range for the datetime data type; or decimal or approximate-numeric data type columns with scale or precision values that are not valid.For databases created in SQL Server 2005, column-value integrity checks are enabled by default and do not require the DATA_PURITY option. For databases upgraded from earlier versions of SQL Server, column-value checks are not enabled by default until DBCC CHECKDB WITH DATA_PURITY has been run error free on the database. After this, DBCC CHECKDB checks column-value integrity by default. For more information about how CHECKDB might be affected by upgrading database from earlier versions of SQL Server, see the Remarks section later in this topic.
If PHYSICAL_ONLY is specified, column-integrity checks are not performed.
Validation errors reported by this option cannot be fixed by using DBCC repair options. For information about manually correcting these errors, see Knowledge Base article 923247: Troubleshooting DBCC error 2570 in SQL Server 2005.
Result Sets
DBCC CHECKDB returns the following result set. The values might vary except when the ESTIMATEONLY, PHYSICAL_ONLY, or NO_INFOMSGS options are specified:
DBCC results for 'model'.
Service Broker Msg 9675, Level 10, State 1: Message Types analyzed: 13.
Service Broker Msg 9676, Level 10, State 1: Service Contracts analyzed: 5.
Service Broker Msg 9667, Level 10, State 1: Services analyzed: 3.
Service Broker Msg 9668, Level 10, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, Level 10, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, Level 10, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, Level 10, State 1: Remote Service Bindings analyzed: 0.
DBCC results for 'sys.sysrowsetcolumns'.
There are 630 rows in 7 pages for object 'sys.sysrowsetcolumns'.
DBCC results for 'sys.sysrowsets'.
There are 97 rows in 1 pages for object 'sys.sysrowsets'.
DBCC results for 'sysallocunits'.
There are 195 rows in 3 pages for object 'sysallocunits'.
There are 0 rows in 0 pages for object "sys.sysasymkeys".
DBCC results for 'sys.syssqlguides'.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for 'sys.queue_messages_1977058079'.
There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".
DBCC results for 'sys.queue_messages_2009058193'.
There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".
DBCC results for 'sys.queue_messages_2041058307'.
There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'model'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC CHECKDB returns the following result set (message) when NO_INFOMSGS is specified:
The command(s) completed successfully.
DBCC CHECKDB returns the following result set when PHYSICAL_ONLY is specified:
DBCC results for 'model'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'master'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC CHECKDB returns the following result set when ESTIMATEONLY is specified.
Estimated TEMPDB space needed for CHECKALLOC (KB)
-------------------------------------------------
13
(1 row(s) affected)
Estimated TEMPDB space needed for CHECKTABLES (KB)
--------------------------------------------------
57
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Remarks
In earlier versions of SQL Server, the values for the per-table and per-index row count and page counts can become incorrect. Under certain circumstances, one or more of these values might even become negative. In SQL Server 2005, these values are always maintained correctly. Therefore, databases that are created on SQL Server 2005 should never contain incorrect counts; however, databases that are upgraded to SQL Server 2005 might. This is not a corruption of any data stored in the database. DBCC CHECKDB has been enhanced to detect when any one of these counts becomes negative. When negative counts are detected, the DBCC CHECKDB output contains a warning and a recommendation to run DBCC UPDATEUSAGE to correct the issue. Although it may appear as if upgrading the database to SQL Server 2005 has caused this issue, the incorrect counts were present before the upgrade procedure.
DBCC CHECKDB does not examine disabled indexes. For more information about disabled indexes, see Disabling Indexes.
If a user-defined type is marked as being byte ordered, there must only be one serialization of the user-defined type. Not having a consistent serialization of byte-ordered user-defined types causes error 2537 when DBCC CHECKDB is run. For more information, see User-Defined Type Requirements.
Because the Resource database is modifiable only in single-user mode, the DBCC CHECKDB command cannot be run on it directly. However, when DBCC CHECKDB is executed against the master database, a second CHECKDB is also run internally on the Resource database. This means that DBCC CHECKDB can return extra results. The command returns extra result sets when no options are set, or when either the PHYSICAL_ONLY or ESTIMATEONLY option is set.
In versions of SQL Server 2005 before SP2, executing DBCC CHECKDB clears the plan cache for the instance of SQL Server. Clearing the plan cache causes recompilation of all later execution plans and may cause a sudden, temporary decrease in query performance. In SP2, executing DBCC CHECKDB does not clear the plan cache.
Internal Database Snapshot
DBCC CHECKDB uses an internal database snapshot for the transactional consistency needed to perform these checks. This prevents blocking and concurrency problems when these commands are executed. For more information, see Understanding Sparse File Sizes in Database Snapshots and the DBCC Internal Database Snapshot Usage section in DBCC (Transact-SQL). If a snapshot cannot be created, or TABLOCK is specified, DBCC CHECKDB acquires locks to obtain the required consistency. In this case, an exclusive database lock is required to perform the allocation checks, and shared table locks are required to perform the table checks.
DBCC CHECKDB fails when run against master if an internal database snapshot cannot be created.
Running DBCC CHECKDB against tempdb does not perform any allocation or catalog checks and must acquire shared table locks to perform table checks. This is because, for performance reasons, database snapshots are not available on tempdb. This means that the required transactional consistency cannot be obtained.
Best Practices
In SQL Server 2005, running DBCC CHECKDB with no options may take considerably longer than in earlier versions. This behavior occurs for the following reasons:
- The logical checks introduced are more comprehensive.
- Some of the underlying structures to be checked are more complex.
- Many new checks have been introduced to include the new features in SQL Server 2005.
Therefore, we recommend that you use the PHYSICAL_ONLY option for frequent use on production systems. Using PHYSICAL_ONLY can greatly shorten run-time for DBCC CHECKDB on large databases. We also recommend that you periodically run DBCC CHECKDB with no options. How frequently you should perform these runs depends on individual businesses and their production environments.
Checking Objects in Parallel
By default, DBCC CHECKDB performs parallel checking of objects. The degree of parallelism is automatically determined by the query processor. The maximum degree of parallelism is configured just like parallel queries. To restrict the maximum number of processors available for DBCC checking, use sp_configure. For more information, see max degree of parallelism Option. Parallel checking can be disabled by using trace flag 2528. For more information, see Trace Flags (Transact-SQL).
Understanding DBCC Error Messages
After the DBCC CHECKDB command finishes, a message is written to the SQL Server error log. If the DBCC command successfully executes, the message indicates success and the amount of time that the command ran. If the DBCC command stops before completing the check because of an error, the message indicates that the command was terminated, a state value, and the amount of time the command ran. The following table lists and describes the state values that can be included in the message.
| State | Description |
|---|---|
0 |
Error number 8930 was raised. This indicates a corruption in metadata that terminated the DBCC command. |
1 |
Error number 8967 was raised. There was an internal DBCC error. |
2 |
A failure occurred during emergency mode database repair. |
3 |
This indicates a corruption in metadata that terminated the DBCC command. |
4 |
An assert or access violation was detected. |
5 |
An unknown error occurred that terminated the DBCC command. |
Error Reporting
In SQL Server 2005 Service Pack 1, a dump file (SQLDUMPnnnn.txt) is created in the SQL Server LOG directory whenever DBCC CHECKDB detects a corruption error. When the Feature Usage data collection and Error Reporting features are enabled for the instance of SQL Server, the file is automatically forwarded to Microsoft. The collected data is used to improve SQL Server functionality. For more information, see Error and Usage Report Settings.
The dump file contains the results of the DBCC CHECKDB command and additional diagnostic output. Access is limited to the SQL Server service account and members of the sysadmin role. By default, the sysadmin role contains all members of the Windows BUILTIN\Administrators group and the local administrator's group. The DBCC command does not fail if the data collection process fails.
Resolving Errors
If any errors are reported by DBCC CHECKDB, we recommend restoring the database from the database backup instead of running REPAIR with one of the REPAIR options. If no backup exists, running repair corrects the errors reported. The repair option to use is specified at the end of the list of reported errors. However, correcting the errors by using the REPAIR_ALLOW_DATA_LOSS option might require deleting some pages, and therefore some data.
Under some circumstances, values might be entered into the database that are not valid or out-of-range based on the data type of the column. In SQL Server 2000, DBCC CHECKDB does not perform range or integrity checks on these column values. However, in SQL Server 2005, DBCC CHECKDB can detect column values that are not valid for all column data types. Therefore, running DBCC CHECKDB with the DATA_PURITY option on databases that have been upgraded from earlier versions of SQL Server might reveal preexisting column-value errors. Because SQL Server 2005 cannot automatically repair these errors, the column value must be manually updated. If CHECKDB detects such an error, CHECKDB returns a warning, the error number 2570, and information to identify the affected row and manually correct the error.
The repair can be performed under a user transaction to let the user roll back the changes that were made. If repairs are rolled back, the database will still contain errors and must be restored from a backup. After repairs are completed, back up the database.
Resolving Errors in Database Emergency Mode
When a database has been set to emergency mode by using the ALTER DATABASE statement, DBCC CHECKDB can perform some special repairs on the database if the REPAIR_ALLOW_DATA_LOSS option is specified. These repairs may allow for ordinarily unrecoverable databases to be brought back online in a physically consistent state. These repairs should be used as a last resort and only when you cannot restore the database from a backup. When the database is set to emergency mode, the database is marked READ_ONLY, logging is disabled, and access is limited to members of the sysadmin fixed server role.
Note
You cannot run the DBCC CHECKDB command in emergency mode inside a user transaction and roll back the transaction after execution.
When the database is in emergency mode and DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS clause is run, the following actions are taken:
- DBCC CHECKDB uses pages that have been marked inaccessible because of I/O or checksum errors, as if the errors have not occurred. Doing this increases the chances for data recovery from the database.
- DBCC CHECKDB attempts to recover the database using regular log-based recovery techniques.
- If, because of transaction log corruption, database recovery is unsuccessful, the transaction log is rebuilt. Rebuilding the transaction log may result in the loss of transactional consistency.
If the DBCC CHECKDB command succeeds, the database is in a physically consistent state and the database status is set to ONLINE. However, the database may contain one or more transactional inconsistencies. We recommend that you runDBCC CHECKCONSTRAINTS to identify any business logic flaws and immediately back up the database.
If the DBCC CHECKDB command fails, the database cannot be repaired.
Running DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS in Replicated Databases
Running the DBCC CHECKDB command with the REPAIR_ALLOW_DATA_LOSS option can affect user databases (publication and subscription databases) and the distribution database used by replication. Publication and subscription databases include published tables and replication metadata tables. Be aware of the following potential issues in these databases:
- Published tables. Actions performed by the CHECKDB process to repair corrupt user data might not be replicated:
- Merge replication uses triggers to track changes to published tables. If rows are inserted, updated, or deleted by the CHECKDB process, triggers do not fire; therefore, the change is not replicated.
- Transactional replication uses the transaction log to track changes to published tables. The Log Reader Agent then moves these changes to the distribution database. Some DBCC repairs, although logged, cannot be replicated by the Log Reader Agent. For example, if a data page is deallocated by the CHECKDB process, the Log Reader Agent does not translate this to a DELETE statement; therefore, the change is not replicated.
- Replication metadata tables. Actions performed by the CHECKDB process to repair corrupt replication metadata tables require removing and reconfiguring replication.
If you have to run the DBCC CHECKDB command with the REPAIR_ALLOW_DATA_LOSS option on a user database or distribution database:
- Quiesce the system: Stop activity on the database and at all other databases in the replication topology, and then try to synchronize all nodes. For more information, see How to: Quiesce a Replication Topology (Replication Transact-SQL Programming).
- Execute DBCC CHECKDB.
- If the DBCC CHECKDB report includes repairs for any tables in the distribution database or any replication metadata tables in a user database, remove and reconfigure replication. For more information, see Removing Replication.
- If the DBCC CHECKDB report includes repairs for any replicated tables, perform data validation to determine whether there are differences between the data in the publication and subscription databases. For more information, see Data at the Publisher and Subscriber Do Not Match.
Permissions
Requires membership in the sysadmin fixed server role or the db_owner fixed database role.
Examples
A. Checking both the current and the AdventureWorks databases
The following example executes DBCC CHECKDB for the current database and for the AdventureWorks database.
-- Check the current database.
DBCC CHECKDB;
GO
-- Check the AdventureWorks database without nonclustered indexes.
DBCC CHECKDB (AdventureWorks, NOINDEX);
GO
B. Checking the current database, suppressing informational messages
The following example checks the current database and suppresses all informational messages.
DBCC CHECKDB WITH NO_INFOMSGS;
GO
See Also
Reference
DBCC (Transact-SQL)
sp_helpdb (Transact-SQL)
System Tables (Transact-SQL)
Other Resources
Physical Database Architecture
Understanding Sparse File Sizes in Database Snapshots
Troubleshooting DBCC Errors on Indexed Views
Optimizing DBCC CHECKDB Performance
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
17 November 2008 |
|
12 December 2006 |
|
17 July 2006 |
|
14 April 2006 |
|
5 December 2005 |
|