Sql-server – repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB

corruptiondbcc-checkdbsql server

Working through a database corruption issue. I am working on a copy in a development environment. Some were corrupt indexes in which case I just dropped and recreated it. These however are data corruption issues that are of concern.

When I run:

DBCC CHECKDB ('DATABASE', REPAIR_ALLOW_DATA_LOSS);

…it says:

the error has been repaired

Does that actually mean it fixed the problem or that it deleted the page which was the "repair" and data was lost?

Object ID 1899231358, index ID 0, partition ID 124468026277888, alloc unit ID 124468026277888 (type In-row data): Page (1:10429642) could not be processed. See other errors for details.
The error has been repaired.

Table error: Object ID 1518653945, index ID 1, partition ID 72057594166444032, alloc unit ID 71875645566156800 (type LOB data).
The off-row data node at page (1:10429980), slot 0, text ID 954358824960 is referenced by page (1:6660634), slot 10, but was not seen in the scan.
The error has been repaired.

Best Answer

As the option you used states...you lost data with the repair

REPAIR_ALLOW_DATA_LOSS

In your case you lost index 0, in-row data. The repair completed successfully, but that doesn't make your lost data come back. About the only scenario of corruption that is fixable without data loss using the option is if all of the corruption was on a non-clustered (not in-row) data.

If you have a previous backup, you may be able to restore the damaged pages. It depends upon your backups not having the same corruption for the affected pages, which is highly likely if you weren't regularly running checkdb, didn't use checksum page verification, and the with checksum option for the backups.

Review the table involved and try to figure out if you can reconstruct otherwise what was lost. Otherwise, good luck and you can try a page restore, see:

How to Restore a Page in SQL Server Standard and Enterprise Edition by Jes Schultz Borland

Related Question