DBCC CHECKDB - Good to know!

If you're a DBA, then you'll need the below content at least once in your career. Your company needs to run DBCC CHECKDB on all system and user databases to keep it safe from corruption and take necessary actions when in trouble. Sometimes 825 errors get converted to 824 eventually. And as a DBA you don't want to see 823, trust me!

Sometimes DBCC CHECKTABLE, DBCC CHECKALLOC and DBCC CHECKCATALOG are not enough unless you tweek it according to some logic, anyways, the below command will make sure you haven't left anything un-turned from SQL end.

EXEC sp_msforeachdb 'DBCC CHECKDB(''?'') WITH ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITY'

If you feel that you need to run the DBCC CHECKDB on the MDF (live, not snapshot) then use TABLOCK.

Tip: In Enterprise edition of SQL Server, the DBCC CHECKDB need not be single-threaded operation but in other editions of SQL Servers, DBCC CHECKDB is always single-threaded operation. In the SQL Server ERRORLOG, you'll see generation of minidump which is normal every time you run DBCC CHECKDB. SQLIOSim is a good command line utility to point out the problem with your underlying disk subsystem. 

-KKB

Comments

• Anonymous
  July 27, 2015
  Good to know