CHECKDB started reporting corruption on a clustered index of our orders table. Queries against it intermittently fail. Nonclustered indexes on the same table look fine. What is the least destructive fix order?
Index corrupted in SQL Server, how to fix?
Solved SQL & Databases
MS
Michael Scofield May 30, 2019
2 replies
6,450 views
Reviewed by moderators
Least destructive first:
1
Nonclustered index corruption: just rebuild it,
ALTER INDEX ix ON table REBUILD;. The data lives elsewhere so nothing is lost.2
Clustered index IS the table data, so a rebuild only fixes logical inconsistency, not damaged pages. Try page restore from a clean backup:
RESTORE DATABASE db PAGE='fileid:pageid' FROM DISK=... for the specific pages CHECKDB lists.3
No clean backup: REPAIR_ALLOW_DATA_LOSS as last resort, or export what remains with SysTools SQL Recovery which reads around damaged pages.
Page restore from Sunday's full backup fixed the exact 3 pages. Zero loss. Solved.