DBCC CHECKDB

WHAT IS THE PURPOSE OF DBCC CHECKDB

The primary purpose is to check both the logical and the physical integrity of all objects in the specified database. In a busy and large production database, it may become necessary to run a few selected options that the DBCC CHECKDB provides.

COMPLETE SYNTAX OF DBCC CHECKDB

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

DBCC CHECKDB SYNTAX OPTIONS

--Checks all data in database --8 seconds

DBCC CHECKDB ('adventureworks2012')

DBCC CHECKDB ('adventureworks2012', NOINDEX) --5 seconds

--Specifies that non clustered indexes for non system tables should not be checked

USE [master]
GO

DBCC CHECKDB WITH NO_INFOMSGS

--Suppresses all informational messages (use in a large database)

DBCC CHECKDB ('TEMPDB') WITH ESTIMATEONLY

--Displays the estimated amount of tempdb space needed to run DBCC CHECKDB (if you want to unload the integrity check to the tempdb)


DBCC CHECKDB ('adventureworks2012') WITH PHYSICAL_ONLY

--This checks physical on-disk structures, but omits the internal logical checks