repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB

Question

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.

Answer 1

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

Answer 2

As far as my "experience" goes, repair_allow_data_loss never actually fixes the problem. What it actually does is it deletes as much as culprits(corrupt pages) possible to make sure whatever left is consistent. This should always be your last resort to get out of the data corruption. Moreover what it also does is removes those valuable constraints/business constraints on which your very logic is designed leaving database in vulnerable state.

A bit of advantage MS provides here is we can run the repair_allow_data_loss in a transaction, but not actually commit it, and gauge amount of data loss and broken database you get. Then you can actually decide further course of action.

I am quoting a warning section from BOL Document about repair_allow_data_loss

The REPAIR_ALLOW_DATA_LOSS option is a supported feature of SQL Server. However, it may not always be the best option for bringing a database to a physically consistent state. If successful, the REPAIR_ALLOW_DATA_LOSS option may result in some data loss. In fact, it may result in more data lost than if a user were to restore the database from the last known good backup. Microsoft always recommends a user restore from the last known good backup as the primary method to recover from errors reported by DBCC CHECKDB. The REPAIR_ALLOW_DATA_LOSS option is not an alternative for restoring from a known good backup. It is an emergency “last resort” option recommended for use only if restoring from a backup is not possible.

Certain errors, that can only be repaired using the REPAIR_ALLOW_DATA_LOSS option, may involve deallocating a row, page, or series of pages to clear the errors. Any deallocated data is no longer accessible or recoverable for the user, and the exact contents of the deallocated data cannot be determined. Therefore, referential integrity may not be accurate after any rows or pages are deallocated because foreign key constraints are not checked or maintained as part of this repair operation. The user must inspect the referential integrity of their database (using DBCC CHECKCONSTRAINTS) after using the REPAIR_ALLOW_DATA_LOSS option.

Before performing the repair, create physical copies of the files that belong to this database. This includes the primary data file (.mdf), any secondary data files (.ndf), all transaction log files (.ldf), and other containers that form the database including full text catalogs, file stream folders, memory optimized data, etc.

Before performing the repair, consider changing the state of the database to EMERGENCY mode and trying to extract as much information possible from the critical tables and save that data.