DBCC Checkdb

Today we are going discuss about DBCC CHECKDB and its usage .

Database Console Command DBCC checkdb is to check the integrety of Database in SQL Server .This command can also be used in databases which has memory optimized tables but dbcc repair option is not available for memory optimized tables if there is any corruption.In any case if we have an issue in memory optimized tables we may have to restore the data from last known good backup .

when we run DBCC CHECKDB internally it will run various operations which are dicussed below :

DBCC CHECKALLOC

DBCC CHECKTABLE

DBCC CHECKCATALOG

As DBCC checkdb runs all the above three commands it will not require to run CHECKALLOC, CHECKTABLE and CHECKCATALOG command s individually

Syntax of DBCC CHECKDB

DBCC CHECKDB
[ ( database_name | database_id | 0
[ , NOINDEX
| , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]
) ]
[ WITH
{
[ ALL_ERRORMSGS ]
[ , EXTENDED_LOGICAL_CHECKS ]
[ , NO_INFOMSGS ]
[ , TABLOCK ]
[ , ESTIMATEONLY ]
[ , { PHYSICAL_ONLY | DATA_PURITY } ]
[ , MAXDOP = number_of_processors ]
}
]
]

Parameters used in above syntax signify the following:

database_name | database_id | 0

means you need to provide Database Name or Database ID . If 0 is specified this means it will use current database to run the integrity checks

NOINDEX

Means it will not check non clustered indexes for User Tables . By using this option we can decrease the execution time of job .

REPAIR_ALLOW_DATA_LOSS

Means this repairs the error which were found in database but this will lead to loss of DATA
So, use this as Last option

REPAIR_FAST

Means maintains backward compatibility and does not perform any repairing.

REPAIR_REBUILD

Means repairs the database faster also it does not impose any loss of data

ALL_ERRORMSGS

Means this option displays all error messages of objects.By default error messages are dispayed in output so, speficifyng or without specifying this option is same

EXTENDED_LOGICAL_CHECKS

Means runs a logical consistency checks on views and indexes if the compatability Level is 100 or higher.

NO_INFOMSGS

Means all informational messages are supressed

TABLOCK

This applies execlusive locks on database instead of using an internal snapshot .Running DBCC CHECKDB with TABLOCK runs faster when we have heavy work loads but this will decrease the availability of database when CHECKDB is running

ESTIMATEONLY

This option tells us the estimated amount of space require on database for checkdb to run.

PHYSICAL_ONLY

checks the integrity of physical structure of the page, record headers and the allocation consistency of the database
And it can also detect torn pages, checksum failures, and common hardware failures of Users data.

Note :

Running Full DBCC CHECKDB command may take longer time to execute because it will do logical checks which are more complex
So, using PHYSICAL_ONLY option will reduce the execution time of CHECKDB on large databases.
It is also recommend to use PHYSICAL_ONLY option when runnign DBCC CHECKDB Frequently on PRODUCTION servers.
But it is still necessary to run FULL DBCC CHECKDB command at least once in a week .
This all will depends on individual environments.

DATA_PURITY

Checks the database for column values that are not valid

Error Summary :

When CHECKDB Command detects corruption dump file named SQLDUMPNNNN.txt is created in the log directory of SQL server.

Restore Database :

When we have a scenario of error in sql server . MS recommended to restore the Database from Last known Good Backup instead of repairing the Database.
If there is no backup available , then go for repair option but using the repair with REPAIR_ALLOW_DATA_LOSS will lead to DATA LOSS.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.