DBCC CHECKDB with MSSQL

  • Microsoft provide DBCC CHECKDB to check the integrity of all objects in a database.
  • DBCC CHECKDB checks for:
    1. Service broker message types
    2. Service broker service contracts
    3. Service broker services
    4. Service broker service queues
    5. Service broker conversation endpoints
    6. Service broker conversation groups
    7. Service broker remote service bindings
    8. Allocation of all data pages for all tables including system tables
    9. Allocation of all index pages for all tables including system tables
    10. Allocation of all index pages for all indexed views
    11. Allocation of text
    12. Allocation of image objects
    13. Structural integrity of all tables
    14. Structural integrity of all indexes

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

DBCC CHECKDB DOs

  • Run DBCC CHECKDB after hours, it's very resource intensive.
  • DBCC CHECKDB repairs can only work if the database is in single user mode: see: How to drop recalcitrant databases in MSSQL
  • if DBCC CHECKDB find errors, first run:
DBCC CHECKDB ( 'sql911',REPAIR_FAST);
go
DBCC CHECKDB ( 'sql911',REPAIR_REBUILD);
go
  • If you still have errors, run:
BEGIN TRANSACTION trans-DBCC
DBCC CHECKDB ( 'sql911',repair_allow_data_loss);
go
  1. The begin transaction will allow you to rollback the transaction if the data loss is not acceptable.
  2. Don't forget to commit or rollback the transaction.

DBCC CHECKDB DON'Ts

  • DBCC CHECKDB already runs:
    1. DBCC CHECKALLOC for every object in the database
    2. DBCC CHECKTABLE for every table in the database
  • you do not run DBCC CHECKALLOC or DBCC CHECKTABLE afterward.
  • Do NOT run DBCC CHECKDB willy-nilly, it's very resource intensive.
  • DBCC CHECKDB uses tempdb to do the sorting.
  • DBCC CHECKDB blocks log truncations until it has finished reading the logs.