DBCC CHECKDB: Detecting and Surviving Corruption

Corruption is the incident every DBA hopes never actually happens and plans for anyway. Disks fail silently, firmware bugs write bad pages, storage arrays lie about durability guarantees they don't actually keep. DBCC CHECKDB is how you find out before a query does, and how it's run in the minutes after discovery determines whether the incident costs you an hour or costs you data permanently.

What CHECKDB Actually Checks

Running DBCC CHECKDB('MyDatabase') isn't one check, it's several, bundled together:

Page-level structural integrity (CHECKALLOC): are allocation structures internally consistent?
Table and index structure (CHECKTABLE across every table): do index entries actually match the data rows they claim to reference?
Catalog consistency (CHECKCATALOG): are system metadata relationships (columns, types, constraints) internally coherent?
Checksums or torn-page detection, if PAGE_VERIFY = CHECKSUM is set on the database, which it should be, this is how CHECKDB (and every backup and read operation) actually notices a page was corrupted on disk in the first place.

If PAGE_VERIFY isn't set to CHECKSUM, you're flying without the instrument that detects most corruption. Check it now: SELECT name, page_verify_option_desc FROM sys.databases;. Older databases migrated forward for years sometimes still carry NONE or the older TORN_PAGE_DETECTION from a setting nobody revisited.

The Workflow, in Order

Run CHECKDB and read the actual output, not just the exit status. A clean run returns no errors; any error means stop and think before touching anything else.
Check whether you have a good backup. If MyDatabase has a recent, verified full backup and enough log backups to bring it current, restoring is almost always faster and safer than repairing in place, this is why SQL Server Backup Concepts and this article are really one topic split in two: the backup strategy you built before the incident is what determines your options during it.
If restoring, restore to a different location first and verify the restored copy is actually clean with its own CHECKDB run, before cutting over, don't overwrite your only remaining known-bad copy until the replacement is confirmed good.
If you must repair in place, start with the least destructive option, REPAIR_REBUILD (fixes issues that don't risk data loss, like nonclustered index corruption which can simply be rebuilt from the clustered index/heap) before ever considering REPAIR_ALLOW_DATA_LOSS.
Only reach for REPAIR_ALLOW_DATA_LOSS when there is no usable backup and the alternative is losing the whole database. It works by deleting whatever's corrupted to bring the database back to a structurally consistent state, consistent, not complete. Know exactly what that means before running it: data is gone, not fixed.

-- Diagnose only, no changes made
DBCC CHECKDB('MyDatabase') WITH NO_INFOMSGS, ALL_ERRORMSGS;

-- Least destructive repair, safe to attempt (still needs single-user mode)
ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB('MyDatabase', REPAIR_REBUILD);
ALTER DATABASE MyDatabase SET MULTI_USER;

-- Last resort only, after confirming no viable backup exists
DBCC CHECKDB('MyDatabase', REPAIR_ALLOW_DATA_LOSS);

Repair option	What it does	Data loss risk
No repair, just diagnose	Reports errors, changes nothing	None, this should always be the first run
`REPAIR_REBUILD`	Fixes issues recoverable without discarding data (e.g. rebuilding a corrupted nonclustered index from intact source data)	None for the issues it can actually fix; refuses to touch issues it can't
`REPAIR_ALLOW_DATA_LOSS`	Deletes corrupted pages/rows to restore structural consistency	Real and permanent; whatever was on those pages is gone

Prevention Beats Repair

Run CHECKDB on a schedule, weekly on most systems, more often on large or business-critical databases, don't wait for a query to fail before finding out corruption exists.
Verify backups by restoring them somewhere, periodically, not just checking that the backup job reported success. A backup of a corrupted database is not a safety net.
Confirm PAGE_VERIFY = CHECKSUM on every database, it costs a small amount of CPU and is the mechanism that actually detects a corrupted page in the first place.
Take storage-layer errors in the Windows event log seriously. Disk and controller warnings often precede a SQL Server corruption report by days or weeks; CHECKDB finding the damage is frequently the last, not the first, sign something was wrong.

Common Gotchas

Running CHECKDB against production without understanding its impact. A full CHECKDB reads the entire database; on a large database this is real I/O and CPU load, schedule it for a maintenance window or use WITH PHYSICAL_ONLY for a lighter (though less thorough) check between full runs.
Repairing before backing up the corrupted state. Even a corrupted database backup preserves forensic evidence and a fallback; take one before running any repair, in case the repair doesn't go as expected.
Treating a clean CHECKDB as proof backups are unnecessary. Corruption can occur between CHECKDB runs; the schedule reduces how long undetected corruption can persist, it doesn't replace the need for tested, restorable backups.
Assuming corruption is always dramatic. Some corruption only affects a handful of rows in a rarely-queried table and stays invisible for months; a scheduled CHECKDB is what surfaces it before an equally rare data-quality investigation stumbles into the same pages.

The Bottom Line

CHECKDB's job is to tell you the truth about your database's structural integrity, and the moment it reports an error is not the moment to start improvising. Restore from a verified backup when you have one, that's almost always faster and safer than any in-place repair. When you don't, work from the least destructive option upward, and treat REPAIR_ALLOW_DATA_LOSS as exactly what its name says: a last resort, not a first move.

← Back to Blog