SQL Server REPAIR ALLOW DATA LOSS – Quick look

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.

 

Regards

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

   

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

4 Comments on “SQL Server REPAIR ALLOW DATA LOSS – Quick look”

  1. I would never recommend running REPAIR_ALLOW_DATA_LOSS to rebuild indexes – complete overkill and requires the database being offline. You should disable and rebuild the broken indexes within a transaction.

    REPAIR_ALLOW_DATA_LOSS is not extent-based.

    If a row is damaged, it does NOT mean the page must be deallocated. It entirely depends on what the damage is.

    Recommend you watch read my blog or watch my Pluralsight course that explains exactly what REPAIR_ALLOW_DATA_LOSS does and when.

Leave a Reply

Your email address will not be published.