Dear Friends,

At different times when DBCC CHECKDB reports any consistency issues, selectively we run CHECKDB with SQL Server REPAIR ALLOW DATA LOSS to fix them. Today we will discuss in short what this really means and some consistency issues we can address using this option.

This repair option in essence means that CHECKDB detected at least an error that may result in data loss. This option is should be used to when you opt for minimum level repair. Question is what it does? In short, when you use this option internally it de-allocates the page or set of pages based on the error.  In case any rows are damaged, it does not necessarily mean that the page must be de-allocated and depends on nature of damage. Some scenarios, where you can use this option safely are discussed here.

Page Free Space error: You might observe following error while running CHECKDB;

Incorrect PFS free space information for page (1:1537) in object ID 60, index ID 1, partition ID <ParttionID>, alloc unit ID <AllocUnitID> (type LOB data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL.

Here, it is recommended to run CHECKDB with REPAIR_ALLOW_DATA_LOSS so that it can repair the issue simply by fixing free space information in PFS page with no data loss.

Damaged Index Page: In case CHECKDB encounters a CHECKSUM failure for a non-clustered or non-leaf page of a clustered index, you can run CHECKDB with REPAIR_ALLOW_DATA_LOSS. Underlying reason is, it cannot trust allocation unit ID information on the page to assure it is a non-clustered index page, it might de-allocate the page based on nature of damage. Repair logic is to rebuild the index so the result is simply to re-build non-clustered index.

It is recommended NOT to run REPAIR_ALLOW_DATA_LOSS in case you need to REBUILD index as it requires database to be OFFLINE. You can achieve the same result by disabling and re-building broken indexes.

There are some errors, which CHECKDB cannot repair and I discussed one of such issues on my earlier blog here.

Special thanks to Paul Randal for his guidance on this blog post.



Kanchan Bhattacharyya

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook