DBCC CHECKDB reporting consistency errors is one of the few moments in this job that deserves to feel urgent. It usually also arrives with no warm-up: a query that worked yesterday throws a 5-digit error number nobody recognizes, or a scheduled DBCC CHECKDB job that’s passed silently for years suddenly doesn’t. This post reproduces a real, physically corrupted page on a lab database end to end, from the first error a user would actually see, through what DBCC CHECKDB reports, to the actual repair, so the shape of the decision is concrete instead of theoretical.
Step 1: What a User Actually Sees First
Corruption rarely announces itself as “corruption.” It shows up as a query failing with a specific, severe error:
Msg 824, Level 24, State 2, Server PWSQL01, Line 2
SQL Server detected a logical consistency-based I/O error: incorrect checksum
(expected: 0x57224236; actual: 0x1c599e44). It occurred during a read of page
(1:368) in database ID 6 at offset 0x000000002e0000 in file
'...\CorruptDemo.mdf'. Additional messages in the SQL Server error log or
operating system error log may provide more detail. This is a severe error
condition that threatens database integrity and must be corrected immediately.
Complete a full database consistency check (DBCC CHECKDB).
Error 824, severity 24, is SQL Server telling you a page failed its checksum on read, the data on disk doesn’t match what SQL Server wrote there. Severity 24 is a fatal error class: the connection running the query is terminated. That’s the signal to stop and run a full check, not retry the query and hope it was a fluke.
Step 2: Run DBCC CHECKDB and Read What It Actually Found
DBCC CHECKDB ('CorruptDemo') WITH NO_INFOMSGS, ALL_ERRORMSGS;
Real output against the same corrupted page:
Msg 8939, Level 16, State 98
Table error: Object ID ..., index ID 1, ..., page (1:368).
Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4.
Msg 8928, Level 16, State 1
Object ID ..., index ID 1, ...: Page (1:368) could not be processed.
Msg 8980, Level 16, State 1
Table error: ... Index node page (1:376), slot 0 refers to child page (1:368)
and previous child (0:0), but they were not encountered.
Msg 8978, Level 16, State 1
Table error: ... Page (1:384) is missing a reference from previous page
(1:368). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 4 consistency errors in table 'Widgets'.
CHECKDB found 0 allocation errors and 4 consistency errors in database 'CorruptDemo'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (CorruptDemo).
Four consistency errors, all tracing back to the same physically damaged page (1:368): the page itself can’t be read, the index node pointing to it is now broken, and the page chain either side of it no longer links up correctly. DBCC CHECKDB tells you plainly, in its last line, what the minimum repair level actually is, that line is the decision point in the next step.
Step 3: The Decision, Before Touching Anything
This is the point that actually matters, and it’s not a T-SQL question:
- Is there a recent, valid backup? If yes, restoring the affected database (or in some cases just the affected file/page, via a page-level restore) recovers the exact data that was there, no loss. This is close to always the right first choice when a backup exists and the outage window is acceptable.
- Is there no usable backup, or is a restore genuinely not viable right now (no backup, a much larger outage than repair would cause, or the corruption is isolated to something non-critical)? Then
REPAIR_ALLOW_DATA_LOSSis the fallback, and the name is not an exaggeration: it repairs consistency by deallocating whatever’s damaged, which really does mean losing whatever rows lived on the affected pages. - Either way, isolate the cause before repeating any fix. A single, one-off bad page after an unexpected shutdown or storage hiccup is a different situation from ongoing, recurring corruption, which points at failing storage hardware, not a SQL Server problem
DBCC CHECKDBcan fix for good.
REPAIR_ALLOW_DATA_LOSS requires single-user mode. That alone is worth sitting with for a moment, it’s telling you this is a big enough hammer that nothing else should be touching the database while it swings.
Step 4: Running the Repair (When Restore Isn’t the Path)
ALTER DATABASE CorruptDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB ('CorruptDemo', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
ALTER DATABASE CorruptDemo SET MULTI_USER;
Real output from the same lab database:
Repair: The Clustered index successfully rebuilt for the object "dbo.Widgets".
Repair: The page (1:368) has been deallocated from object ID ..., index ID 1.
The error has been repaired. [x4]
CHECKDB found 0 allocation errors and 4 consistency errors in table 'Widgets'.
CHECKDB fixed 0 allocation errors and 4 consistency errors in table 'Widgets'.
CHECKDB found 0 allocation errors and 4 consistency errors in database 'CorruptDemo'.
CHECKDB fixed 0 allocation errors and 4 consistency errors in database 'CorruptDemo'.
All 4 errors fixed. Read the summary line carefully: “fixed” means consistent, not “recovered.” Page (1:368) was deallocated, not restored, whatever rows were stored there are gone. A follow-up DBCC CHECKDB with no repair option afterward confirms 0 errors remain, that’s the real verification step, not just trusting the repair’s own summary.
What This Doesn’t Fix
- It doesn’t bring back the data that was on the deallocated page. If those specific rows matter, the only way to get them back is from a backup taken before the corruption happened,
REPAIR_ALLOW_DATA_LOSScannot recover data that’s already gone, it only removes what’s unreadable so the database is structurally consistent again. - It doesn’t tell you why the page corrupted. A checksum mismatch means the bytes on disk don’t match what SQL Server wrote. That’s a storage-layer problem, a failing disk, a bad controller, a filesystem-level issue, or (rarely) faulty RAM, not something inside SQL Server’s control. Repeated corruption on the same instance is a hardware investigation, not a repeat-the-repair situation.
- It doesn’t replace having backups. The entire reason this decision has two branches is that a backup turns “figure out how much data to accept losing” into “restore and move on.”
Best Practices
- Run
DBCC CHECKDBon a real schedule, not just after something goes wrong, on a large database the read alone takes real time and you don’t want that clock starting only after users are already affected. Get Last DBCC CHECKDB confirms it’s actually running and recently. - Treat error 824 as urgent the moment it appears, don’t wait for a second occurrence to decide it’s real.
- Default to restore-from-backup over repair whenever a valid backup exists;
REPAIR_ALLOW_DATA_LOSSis the fallback for when a restore genuinely isn’t viable, not the first option. - After any repair, re-run a clean
DBCC CHECKDB(no repair option) to confirm 0 errors remain, and check Get Suspect Pages and Integrity Checks for anything still flagged. - If corruption recurs on the same instance or the same disk, stop treating it as a SQL Server problem and get the underlying storage checked.
Related Scripts
- Get Last DBCC CHECKDB, confirms integrity checks are actually running on schedule, before you need them
- Get Suspect Pages and Integrity Checks, the ongoing early-warning check for exactly this class of error
- Maintenance Job Framework, for building a reviewable integrity-check job if one isn’t scheduled yet
- Performance and Troubleshooting (area)
Leave a Reply