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 ] ]
}
]
Checks all data in database
DBCC CHECKDB (‘adventureworks2012’)
Specifies that non clustered indexes for non system tables should not be checked
DBCC CHECKDB (‘adventureworks2012’, NOINDEX)
-Suppresses all informational messages (use in a large database)
Use AdventureWorks2012
GO
DBCC CHECKDB WITH NO_INFOMSGS
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 (‘TEMPDB’) WITH ESTIMATEONLY
This checks physical on-disk structures, but omits the internal logical checks:-
DBCC CHECKDB (‘adventureworks2012’) WITH PHYSICAL_ONLY