What is CheckDB and How CheckDB is executed

The command CheckDB checks the logical and physical integrity of all the objects in the specified database. It will check internal structures, metadata, and data in the database.

CheckDB has two roles: Integrity Check, Correction.

Integrity check is helps to verify the allocated structures of GAM, SGAM, PFS and IAM etc., In addition to it verifies the table consistency of all the tables and of its indexes including system catalogs. DBCC CHECKDB automatically executes DBCC CHECKTABLE for each table in the database, DBCC CHECKALLOC and DBCC CHECKCATALOG, no need to run them separately.

DBCC CHECKALLOC –Used to check disk space allocation structures for a particular database.

DBCC CHECKTABLE – Used to check integrity of the structures and pages that build up the indexeds or tables.

DBCC CHECKCATALOG – Used to Checks the consistency of catalog for the specified database, but database has to be online.

How CheckDB is executed?

CheckDB doesn't run directly over the database, a hidden database snapshot is created and process runs on that snapshot.

To check the integrity of AdventureWorks
DBCC CheckDB(AdventureWorks);

The default output of checkdb will have lot of information and the last two lines will show you exact information like this:

CHECKDB found 0 allocation errors and 0 consistency errors in database 'AdventureWorks'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Few Execution Options:

NO_INFOMSGS -- It doesn't display any informational messages unless there is any error

ALL_ERRORMSGS -- This is the default parameter since SQL Server 2008 SP1, displays complete information

NOINDEX -- It Skips nonclustered indexes while doing integrity checks

ESTIMATEONLY -- Estimates the space needed for TempDB to run the checkdb, it is useful when you are planning to run this checkdb on VLDB

TABLOCK -- Uses locks instead of database snapshots, it is useful when the snapshot creation is failing

PHYSICAL_ONLY -- It skips most of the logical verifications, and it runs with less resources. But Full execution is recommended

TABLERESULTS -- It returns the information in a tabular format rather than free-form text.

DBCC CheckDB(AdventureWorks) with TABLERESULTS


DBCC CheckDB(AdventureWorks) with ESTIMATEONLY, TABLERESULTS -- If you want to include multiple switches


If CheckDB is not reported any errors then no need to repair your database. There are couple of options available in CheckDB to repair databases.


DBCC CheckDB(AdventureWorks, REPAIR_REBUILD)
DBCC CheckDB(AdventureWorks, REPAIR_ALLOW_DATA_LOSS)

Here we need to remember few points:

Repair_Rebuild -- It will repair only non-clustered indexes
Repair cannot fix all every things in database
Repairs are always run offline, (databases must be in single user mode)
At times repairs will cause data loss if you use REPAIR_ALLOW_DATA_LOSS
Repair cannot be done in master and tempdb because they cannot be put in single-user mode.