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.