DBCC CHECKDB
Checks the allocation and structural integrity of all the objects in the specified database.
Syntax
DBCC CHECKDB
( 'database_name'
[ , NOINDEX
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD
} ]
) [ WITH { [ ALL_ERRORMSGS ]
[ , [ NO_INFOMSGS ] ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
}
]
Arguments
'database_name'
Is the database for which to check all object allocation and structural integrity. If not specified, the default is the current database. Database names must conform to the rules for identifiers. For more information, see Using Identifiers.
NOINDEX
Specifies that nonclustered indexes for nonsystem tables should not be checked. NOINDEX decreases the overall execution time because it does not check nonclustered indexes for user-defined tables. NOINDEX has no effect on system tables, because DBCC CHECKDB always checks all system table indexes.
REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST| REPAIR_REBUILD
Specifies that DBCC CHECKDB repair the found errors. The given database_name must be in single-user mode to use a repair option and can be one of the following.
| Value | Description |
|---|---|
| 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. |
| 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. |
WITH
Specifies options on the number of error messages returned, locks obtained, or estimating tempdb requirements.
ALL_ERRORMSGS
Displays an unlimited number of errors per object. If ALL_ERRORMSGS is not specified, displays up to 200 error messages for each object. Error messages are sorted by object ID, except for those messages generated from tempdb.
NO_INFOMSGS
Suppresses all informational messages (Severity 10) and the report of space used.
TABLOCK
Causes DBCC CHECKDB to obtain shared table locks. 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.
ESTIMATE ONLY
Displays the estimated amount of tempdb space needed to run DBCC CHECKDB with all of the other specified options. The check is not performed.
PHYSICAL_ONLY
Limits the checking to the integrity of the physical structure of the page and record headers, and to the consistency between the pages' object ID and index ID and the allocation structures. Designed to provide a low overhead check of the physical consistency of the database, this check also detects torn pages and common hardware failures that can compromise a user's data. PHYSICAL_ONLY always implies NO_INFOMSGS and is not allowed with any of the repair options.
Remarks
DBCC CHECKDB performs a physical consistency check on indexed views. The NOINDEX option, used only for backward compatibility, also applies to any secondary indexes on indexed views.
DBCC CHECKDB is the safest repair statement because it identifies and repairs the widest possible errors. If only allocation errors are reported for a database, execute DBCC CHECKALLOC with a repair option to repair these errors. However, to ensure that all errors, including allocation errors, are properly repaired, execute DBCC CHECKDB with a repair option rather than DBCC CHECKALLOC with a repair option.
DBCC CHECKDB validates the integrity of everything in a database. There is no need to run DBCC CHECKALLOC or DBCC CHECKTABLE if DBCC CHECKDB either is currently or has been recently executed.
DBCC CHECKDB performs the same checking as if both a DBCC CHECKALLOC statement and a DBCC CHECKTABLE statement were executed for each table in the database.
DBCC CHECKDB does not acquire table locks by default. Instead, it acquires schema locks that prevent meta data changes but allow changes to the data. The schema locks acquired will prevent the user from getting an exclusive table lock required to build a clustered index, drop any index, or truncate the table.
The DBCC statement collects information, and then scans the log for any additional changes made, merging the two sets of information together to produce a consistent view of the data at the end of the scan.
When the TABLOCK option is specified, DBCC CHECKDB acquires shared table locks. This allows more detailed error messages for some classes of errors and minimizes the amount of tempdb space required by avoiding the use of transaction log data. The TABLOCK option will not block the truncation of the log and will allow the command to run faster.
DBCC CHECKDB checks the linkages and sizes of text, ntext, and image pages for each table, and the allocation of all the pages in the database.
For each table in the database, DBCC CHECKDB checks that:
- Index and data pages are correctly linked.
- Indexes are in their proper sort order.
- Pointers are consistent.
- The data on each page is reasonable.
- Page offsets are reasonable.
Errors indicate potential problems in the database and should be corrected immediately.
By default, DBCC CHECKDB performs parallel checking of objects. The degree of parallelism is determined automatically by the query processor. The maximum degree of parallelism is configured in the same manner as that of parallel queries. Use the sp_configure system stored procedure to restrict the maximum number of processors available for DBCC checking. 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.
Result Sets
Whether or not any options (except for the NO_INFOMSGS or NOINDEX options) are specified, DBCC CHECKDB returns this result set for the current database, if no database is specified (values may vary):
DBCC results for 'master'.
DBCC results for 'sysobjects'.
There are 862 rows in 13 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 80 rows in 3 pages for object 'sysindexes'.
'...'
DBCC results for 'spt_provider_types'.
There are 23 rows in 1 pages for object 'spt_provider_types'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'master'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
IF the NO_INFOMSGS option is specified, DBCC CHECKDB returns this result set (message):
The command(s) completed successfully.
DBCC CHECKDB returns this result set when the ESTIMATEONLY option 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.
Permissions
DBCC CHECKDB permissions default to members of the sysadmin fixed server role or the db_owner fixed database role, and are not transferable.
Examples
A. Check both the current and the pubs database
This example executes DBCC CHECKDB for the current database and for the pubs database.
-- Check the current database.
DBCC CHECKDB
GO
-- Check the pubs database without nonclustered indexes.
DBCC CHECKDB ('pubs', NOINDEX)
GO
B. Check the current database, suppressing informational messages
This example checks the current database and suppresses all informational messages.
DBCC CHECKDB WITH NO_INFOMSGS
GO
See Also
Features Supported by the Editions of SQL Server 2000
How to configure the number of processors available for parallel queries (Enterprise Manager)