Skill 2.3 – Manage database integrity

Databases can get corrupted by hardware problems or virtualization issues
Microsoft recommends restoring instead of repairing.

Implement database consistency checks

  • DBCC CHECKALLOC – checks disk space allocation
  • DBCC CHECKCATALOG – check consistency of system tables in a database
  • DBCC CHECKDB – checks logical and physical integrity of all objects in the DB
  • DBCC CHECKFILEGROUP – checks the allocation and physical integrity of all tables and indexed views in a filegroup
  • DBCC CHECKTABLE – checks allocation and structural integrity of a table
    • Does not check in-memory tables
  • DBCC TABLOCK – will lock tables instead of creating a snapshot
    • also happens if disk is formatted with FAT or ReFS

Identify database corruption

The database engine logs corruption in  few places.  Check them regularly

  • ERRORLOG captures any DBCC CHEC
  • Windows event log (application) captures DBCC errors
  • [msdb] system database had a [suspect_tables] table
  • dump file in the LOG directory SQLDUMPnnnn.txt
  • Error 823 – Cyclic redundancy check – mean disk troubles.  Set the SQL agent to alert you on these
  • Error 824 – Logical consistency check – storage trouble of some sort
  • Error 825 -Read Retry error – means the DB had to re-try a read or write.  Fails after 4 tries
  • Error 832 – corrupt memory page – might mean bad memory

Recover from database corruption

The DBCC will suggest a course of action.  If the corruption is in a nonclustered index, just drop and rebuild the index.  Otherwise just restore the DB

DBCC Error States:

  • 0 – Error 8930 was raised.  corrup metadata
  • 1 – error 8967 was raised. internal DBCC error
  • 2 – failure during emergency mode database repair
  • 3 – Corrupt metadata
  • 4 – assert or acess violation
  • 5 – unknown error

REPAIR_ALLOW_DATA_LOSS – absolute last resort.  Have resume up to date.