SQL Server Database Consistency Check Options

We can check SQL Server database integrity of all the objects in the specified database by using DBCC CHECKDB command. It helps to check database corruption. DBCC CHECKDB command offers many options to check sql server database integrity. Check this syntax which covers all DBCC CHECKDB command integrity check options:

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

 

'database_name'
Replace this argument with your database name for which you want to check object allocation and structural integrity. If you have not specified it, it takes current database as a default value. 
 

NOINDEX
This argument used to specify that non clustered indexes for non system tables should not be checked. It decreases the overall execution time because it does not check non clustered indexes for user-defined tables. 

For using following three repair options REPAIR_ALLOW_DATA_LOSS, REPAIR_FAST,  REPAIR_REBUILD, the given database_name must be in single-user mode.

REPAIR_ALLOW_DATA_LOSS: 
This argument used to correct allocation errors, structural row or page errors, and deletion of corrupted text objects. This repair option can result some data loss. The repair may be done under user transactions which allow the user to roll back all the changes made. If this repair are rolled back & the database still contain errors, should be restored from a backup. It can Perform all the repairs actions that done by REPAIR_REBUILD

REPAIR_FAST: 
This option can be done quickly & have no risk of data loss. It used to perform minor repair actions such as repairing extra keys in non clustered indexes. 

REPAIR_REBUILD: 
It can Performs all the repairs actions that done by REPAIR_FAST &  can be done without risk of data loss.

All arguments that are mentioned after “With” in the syntax used to display the error messages.

ALL_ERRORMSGS
It shows an unlimited number of errors per object. 

NO_INFOMSGS
It is used display the current database File Name, Database Name, Total Extend, Used Extent, Field ID and File Group.

TABLOCK
It is a cause to DBCC CHECKDB run faster on a database under heavy load, but it decreased the concurrency available on the database while DBCC CHECKDB is running.

ESTIMATE ONLY
It shows the estimated amount of tempdb space needed to run DBCC CHECKDB with all of the other specified options. 

PHYSICAL_ONLY
It is designed to check physical consistency of the database. It also detects disk errors, controller issues or other hardware-based problems.

Conclusion: You should regularly run consistency checks. If DBCC CHECKDB command indicates any Errors message in the database, it should be corrected immediately.