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