Database Console Command CHECKDB (DBCC CHECKDB)is used to check the integrity (physical & logical) of objects in a SQL Server database.The command is supported in databases that contain memory-optimized tables but the validation is only supported in disk-based tables. The DBCC repair option is unavailable in memory-optimized tables and therefore, leads to the need of regular database backup.In case an issue arises in a memory-optimized table, the data can be restored from the last backup done.

The various operations that are performed by CHECKDB are:

• DBCC CHECKALLOC execution on SQL database.
• DBCC CHECKTABLE execution on each table and view of SQL database.
• DBCC CHECKCATALOG execution on SQL database.
• Validation of content in the indexed view of the database.
• Validation of link-level consistency amidst the file directories and the table metadata.
• Validation of service broker data.

Since DBCC CHECKDB runs all the other commands it will not be necessary to run CHECKALLOC, CHECKTABLE and CHECKCATALOG commands separately.

DBCC CHECKDB Syntax

The arguments used in the above syntax signify the following:

database_name | database_id | 0

This is the name of the database.In case the name is not signified or only 0 is written, then the current database is being used.

NOINDEX

It specifies that the integrity check of non clustered index tables should not be performed. This leads to decrease of complete execution time.This syntax does not affect the data residing in the tables.

REPAIR_ALLOW_DATA_LOSS

This syntax repairs the errors that are found in the database.This can lead to loss of some data.

Note: This syntax is supported by Microsoft and does not every time prove to be an ideal solution for turning the database in a good physical state. This is because it deletes the entire data that is found to be corrupted and can lead to more data loss, than was originally done to the database.Therefore, it should be adopted as a last resort.

REPAIR_FAST

This argument only maintains backward compatibility and does not perform any repairing.

REPAIR REBUILD

This argument includes faster repairing process which does not impose threat of any data loss.

ALL_ERRORMSGS

This shows all the errors that are generated in all the objects. Including or excluding this syntax will not have any effect as error messages are usually sorted by the object ID. The maximum number of error messages that are generated can reach up to 1000.

EXTENDED_LOGICAL_CHECKS

This argument runs a logical consistent check on views and indexes, only if the compatibility level is 100.

NO_INFOMSGS

It removes all the informational messages.

TABLOCK

This syntax obtains an exclusive lock on the database and will increase the speed of DBCC CHECKDB on a database at times of heavy load. But it decreases the availability of the database for concurrent operations.

ESTIMATEONLY

This specifies or estimates the amount of space the database would require to run CHECKDB command.

PHYSICAL_ONLY

This puts a limitation for checking only the physical structure of the database. A short overhead check of the physical database is accompanied by detection of torn pages, failures and common problems faced by users.

DATA PURITY

This syntax checks for column values that are either out of range or are not valid. Integrity checks of column-value are enabled by default and do not need DATA_PURITY syntax.

Things To Be Kept In Mind

• Disabled indexes cannot be checked by DBCC CHECKDB.
• The user-defined and byte-ordered types need to be serialized if DBCC CHECKDB needs to be executed. In any other case, error 2537 occurs.
• DBCC CHECKDB cannot be directly run on Resource database as it can be modified in single-mode only.

Error Messages Generated By DBCC CHECKDB

When the CHECKDB command is finished running, a message is being written to the SQL error log. In case of success, it generates message indicating success and the total time for which the command ran. In case of failure, the process is terminated due to the occurrence of some error, as indicated by a message. The various state values which represent the error message are:

Error Report

Whenever corruption is detected by CHECKDB command, a dump file named SQLDUMPNNNN.txt is created in the log directory of SQL server. In case the Feature Usage Data Collection and Error Reporting are enabled in SQL, the error report is sent to Microsoft for improvement purposes.

Database Restoration

In the scenario of error generation in SQL server, it is recommended to restore the database from the last created backup instead of repairing the database.In case no backup exists, you can go for repair options. But opting repairing with REPAIR_ALLOW_DATA_LOSS can lead to deletion of some data.

Alternative Resolution For Database Recovery

Basically the DBCC CHECKDB command checks the consistencies of the database, including physical or logical. This command check the pages, index and some other components of the SQL server database, but at some critical points this opt to refuse to recover the SQL database. In case of absence of backup of MS SQL server database, the opted repair options can delete an appreciable amount of data.Therefore, in order to recover the database without compromising with the data integrity, you can opt for Microsoft SQL database recovery solution. They guarantee complete recovery of database without deleting any amount of data.