DBCC CHECKDB (T-SQL)

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}]

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 an option for the number of error messages to be returned. If neither ALL_ERRORMSGS nor NO_INFOMSGS is specified, Microsoft® SQL Server™ returns all error messages.

ALL_ERRORMSGS

Displays all error messages. If not specified, SQL Server displays a maximum of 200 error messages per table. Error messages are sorted by object ID, except for those messages generated from tempdb.

NO_INFOMSGS

Suppresses all informational messages and the report of space used.

Remarks

DBCC CHECKDB is the safest repair statement because it catches 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 requires a shared lock on all tables and indexes in the database for the duration of the operation. While DBCC CHECKDB is running, it is not possible to create, alter, or drop tables.

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 sorted 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.

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 'syscolumns'.

There are 2902 rows in 41 pages for object 'syscolumns'.

DBCC results for 'systypes'.

There are 24 rows in 1 pages for object 'systypes'.

DBCC results for 'syscomments'.

There are 1184 rows in 390 pages for object 'syscomments'.

DBCC results for 'sysfiles1'.

There are 2 rows in 1 pages for object 'sysfiles1'.

DBCC results for 'syspermissions'.

There are 492 rows in 2 pages for object 'syspermissions'.

DBCC results for 'sysusers'.

There are 13 rows in 1 pages for object 'sysusers'.

DBCC results for 'sysdepends'.

There are 3433 rows in 16 pages for object 'sysdepends'.

DBCC results for 'sysreferences'.

There are 0 rows in 1 pages for object 'sysreferences'.

DBCC results for 'sysfulltextcatalogs'.

There are 1 rows in 1 pages for object 'sysfulltextcatalogs'.

DBCC results for 'sysdatabases'.

There are 6 rows in 1 pages for object 'sysdatabases'.

DBCC results for 'sysxlogins'.

There are 3 rows in 1 pages for object 'sysxlogins'.

DBCC results for 'sysdevices'.

There are 6 rows in 1 pages for object 'sysdevices'.

DBCC results for 'sysmessages'.

There are 2768 rows in 109 pages for object 'sysmessages'.

DBCC results for 'sysconfigures'.

There are 41 rows in 1 pages for object 'sysconfigures'.

DBCC results for 'sysservers'.

There are 1 rows in 1 pages for object 'sysservers'.

DBCC results for 'syslanguages'.

There are 23 rows in 2 pages for object 'syslanguages'.

DBCC results for 'syscharsets'.

There are 103 rows in 30 pages for object 'syscharsets'.

DBCC results for 'sysaltfiles'.

There are 2 rows in 1 pages for object 'sysaltfiles'.

DBCC results for 'sysfilegroups'.

There are 1 rows in 1 pages for object 'sysfilegroups'.

DBCC results for 'sysallocations'.

There are 1 rows in 1 pages for object 'sysallocations'.

DBCC results for 'spt_committab'.

There are 0 rows in 1 pages for object 'spt_committab'.

DBCC results for 'spt_monitor'.

There are 1 rows in 1 pages for object 'spt_monitor'.

DBCC results for 'spt_values'.

There are 679 rows in 5 pages for object 'spt_values'.

DBCC results for 'spt_fallback_db'.

There are 0 rows in 1 pages for object 'spt_fallback_db'.

DBCC results for 'spt_fallback_dev'.

There are 0 rows in 1 pages for object 'spt_fallback_dev'.

DBCC results for 'spt_fallback_usg'.

There are 0 rows in 1 pages for object 'spt_fallback_usg'.

DBCC results for 'tbl1'.

There are 5 rows in 1 pages for object 'tbl1'.

DBCC results for 'FulltextTest'.

There are 4 rows in 1 pages for object 'FulltextTest'.

DBCC results for 'MSreplication_options'.

There are 2 rows in 1 pages for object 'MSreplication_options'.

DBCC results for 'spt_datatype_info_ext'.

There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.

DBCC results for 'spt_datatype_info'.

There are 33 rows in 1 pages for object 'spt_datatype_info'.

DBCC results for 'spt_server_info'.

There are 29 rows in 1 pages for object 'spt_server_info'.

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.

DBCC CHECKDB returns this result set if the NOINDEX option is specified (values may vary):

DBCC results for 'Northwind'.

Warning: NO_INDEX option of CHECKDB being used. Checks on non-system indexes will be skipped.

DBCC results for 'sysobjects'.

There are 104 rows in 2 pages for object 'sysobjects'.

DBCC results for 'sysindexes'.

There are 65 rows in 2 pages for object 'sysindexes'.

DBCC results for 'syscolumns'.

There are 610 rows in 9 pages for object 'syscolumns'.

DBCC results for 'systypes'.

There are 24 rows in 1 pages for object 'systypes'.

DBCC results for 'syscomments'.

There are 151 rows in 11 pages for object 'syscomments'.

DBCC results for 'sysfiles1'.

There are 2 rows in 1 pages for object 'sysfiles1'.

DBCC results for 'syspermissions'.

There are 68 rows in 1 pages for object 'syspermissions'.

DBCC results for 'sysusers'.

There are 13 rows in 1 pages for object 'sysusers'.

DBCC results for 'sysdepends'.

There are 383 rows in 2 pages for object 'sysdepends'.

DBCC results for 'sysreferences'.

There are 2 rows in 1 pages for object 'sysreferences'.

DBCC results for 'sysfulltextcatalogs'.

There are 0 rows in 1 pages for object 'sysfulltextcatalogs'.

DBCC results for 'sysfilegroups'.

There are 1 rows in 1 pages for object 'sysfilegroups'.

DBCC results for 'sysallocations'.

There are 1 rows in 1 pages for object 'sysallocations'.

DBCC results for 'Categories'.

There are 8 rows in 1 pages for object 'Categories'.

DBCC results for 'Customers'.

There are 91 rows in 3 pages for object 'Customers'.

DBCC results for 'Employees'.

There are 9 rows in 1 pages for object 'Employees'.

DBCC results for 'Suppliers'.

There are 29 rows in 1 pages for object 'Suppliers'.

DBCC results for 'Products'.

There are 77 rows in 2 pages for object 'Products'.

DBCC results for 'Shippers'.

There are 3 rows in 1 pages for object 'Shippers'.

DBCC results for 'Orders'.

There are 830 rows in 21 pages for object 'Orders'.

DBCC results for 'Order Details'.

There are 2155 rows in 10 pages for object 'Order Details'.

DBCC results for 'SouthAmericanCustomers'.

There are 16 rows in 2 pages for object 'SouthAmericanCustomers'.

DBCC results for 'CustomerResults'.

There are 16 rows in 2 pages for object 'CustomerResults'.

DBCC results for 'CustomersOne'.

There are 5 rows in 2 pages for object 'CustomersOne'.

DBCC results for 'CustomersTwo'.

There are 5 rows in 2 pages for object 'CustomersTwo'.

DBCC results for 'CustomersThree'.

There are 5 rows in 2 pages for object 'CustomersThree'.

DBCC results for 'tbl1'.

There are 5 rows in 1 pages for object 'tbl1'.

CHECKDB found 0 allocation errors and 0 consistency errors in database 'Northwind'.

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):

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

Physical Database Architecture	System Tables
sp_helpdb	DBCC