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 Twitter | Follow me on FaceBook
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.
It didn’t put my name in – Paul Randal, author of DBCC CHECKDB.
Thanks Paul, I’ll read recommended blogs.
Regards,
Kanchan
Awesome Artcile .. Very Useful !!