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
- Start > All Programs > Microsoft SQL Server 2008 R2 > SQL Server Management Studio
- When the Connect to Server Dialog Box comes up, click “Connect” to open up SQL.
- Click on the New Query option.
- Type “DBCC CHECKDB” in the New Query dialog.
- Click on the “Execute” button on the toolbar to run the query.
- When and if the query completes successfully, you will get a status on the bottom of the query dialog.
- 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.
