Error 824 appeared in our logs overnight and CHECKDB confirms a handful of corrupt pages in one large table. Production database, full recovery model, backups intact.
What is the surgical fix and what must I resist doing?
Remove SQL page level corruption from a database
Solved Email & Outlook
RK
Rachel Kim May 27, 2021
2 replies
9,460 views
Reviewed by moderators
With intact backups under full recovery you hold the good position, so surgery first, resistance list second, root cause always.
The surgical fix is page level restore, repairing just the wounded pages from backup while the rest of the database serves on: RESTORE DATABASE yours PAGE = 'fileid:pageid' FROM DISK = 'full.bak' WITH NORECOVERY, listing the pages CHECKDB named or msdb.dbo.suspect_pages recorded, then apply the differentials and every log backup since, finish with a fresh tail log backup applied last and RECOVERY. The pages come back consistent with the present, not the past, because the log replay carries them forward. Enterprise edition runs this online with the table available throughout, Standard does it offline but still page sized rather than database sized. Identify what lived on the pages first via DBCC PAGE or the object referenced in the 824 text, corruption in a nonclustered index skips the ceremony entirely, drop and rebuild the index and the corruption is gone with it.
The resistance list, in order of temptation: repair_allow_data_loss deletes what it cannot reconcile, pages of a production table in this case, a last resort for the backupless rather than a fix for you. Restoring the whole database to yesterday discards today for the sake of a few pages the surgical restore fixes without loss. And rebooting or detaching the database in the hope corruption shakes loose does nothing to pages except sometimes make them unreachable, detach especially being how recoverable situations become forum threads titled please help.
Root cause with the same seriousness as the fix: 824 means the page came back from storage failing its checksum, the IO subsystem returned wrong bits. Windows System event log around the timestamps, storage firmware and controller diagnostics and a follow up CHECKDB tonight and for the next week, because one 824 is a warning shot and clusters of them are the storage announcing intentions. The database layer here worked, checksums caught the lie, the layer below owes answers.
Two pages belonged to a nonclustered index, dropped and rebuilt, gone. The remaining three went through the page restore online and CHECKDB reports clean. Storage team found a controller firmware advisory matching our model within the hour. Nothing lost, everything learned.