SQL Server 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 SQL Server 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

Run the “DBCC CHECKDB” query in Microsoft SQL Server Management Studio

  1. Start > All Programs > Microsoft SQL Server 2008 R2 > SQL Server Management Studio
  2. When the Connect to Server Dialog Box comes up, click “Connect” to open up SQL.
  3. Click on the New Query option.
  4. Type “DBCC CHECKDB” in the New Query dialog.
  5. Click on the “Execute” button on the toolbar to run the query.
  6. When and if the query completes successfully, you will get a status on the bottom of the query dialog.
  7. Verify the results report 0 allocation errors and 0 consistency errors, if both show 0 then the database is healthy and the query/SSMS can be closed. 
    If errors were found continue on with Steps 8 – 10.