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.