MS SQL Server DBCC CheckDB command

DBCC CheckDB (’dbname’) is used to check and optionally repair the Database Allocation Consistency and the Index Pointer Correctness of the Database. It is the combination of DBCC CheckAlloc and DBCC CheckTable commands.

DBCC CheckDB can run when the system is running, but it is not recommended to run when Intensive Transactions are in progress, or when the database backup is running. DBCC CheckDB is using tempdb to sort and store working data, so it is recommended to make tempdb big enough to speed up the Check/Repair Process.

If the database only reports allocation error, we can just run DBCC CheckAlloc to check and repair, save time and resources. If we just want to check/repair the data, index, and field type text, ntext, and image fields of certain table, we can just run DBCC CheckTable (’tablename’).

Below are the command syntax and the examples of DBCC CheckDB:

Syntax
DBCC CHECKDB
( ’database_name’
[ , NOINDEX
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD
} ]
) [ WITH { [ ALL_ERRORMSGS ]
[ , [ NO_INFOMSGS ] ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
}
]

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