Check Integrity Of Objects In SQL Server 2008

In this article I show you how to check integrity of objects in a specified databases. The word 'checking integrity means to ensure the quality of the data in the database. DBCC stands for Database Console Commands.

DBCC CHECKDB executes other DBCC commands:

• It runs DBCC CHECKALLOC (checks disk allocation space) on the database.
• It runs DBCC CHECKTABLE (checks tables and indexed views) on the database.
• It runs DBCC CHECKCATALOG (checks catalog consistency) on the database.

This means that above three commands do not need to run separately from DBCC CHECKDB. This command also checks the linking between data pages and index pages, Index pointers and data page offsets. It is the best way of detecting problems.

Syntax for DBCC CHECKDB command

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

}

]

Here

'database_name' | database_id | 0 is the first parameter which includes three options i.e. database name, database_id and 0. database_name is the name of database, database_id is the ID of database and 0 denotes the current database. We can select one of the three options.

NOINDEX If this option is selected, it skips checks of non clustered indexes.

REPAIR_ALLOW_DATA_LOSS |REPAIR_FAST | REPAIR_REBUILD, in this parameter REPAIR_ALLOW_DATA_LOSS repair all reported errors with the risk of losing data in the process, REPAIR_FAST is used for maintaining backward compatibility, REPAIR_REBUILD perform all repairs without risk of losing data.

ALL_ERRORMSGS displays all error message (first 200 errors).

NO_INFOMSGS displays informational messages.

TABLOCK Obtain locks instead of an internal database snapshot.

ESTIMATEONLY Provides the estimated space required by tempdb.

PHYSICAL_ONLY skips logical checks, and checks physical issues only.

DATA_PURITY checks for the columns that are not valid or out of range.

Lets see how DBCC CHECKDB command works, Suppose I want to check the integrity of  database named 'person'. Write following code:

DBCC CHECKDB ('person')

This returns the following result giving detailed information of database objects evaluated in the database like rows, pages and error found.