Deciding to Run REPAIR_ALLOW_DATA_LOSS

Just because REPAIR_ALLOW_DATA_LOSS might get your database up and running quickly doesn’t make it a good option. It could wrongly be considered a shortcut or an easy button to get handle corruption. Some may run REPAIR_ALLOW_DATA_LOSS, see their database back online, and call it a day. Let’s look at why some things are too good to be true.

What is REPAIR_ALLOW_DATA_LOSS?

When running DBCC CHECKDB you may have errors that include this type of message:

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (ExampleDB).

Sure, we can try to repair the ExampleDB database this way. After all, it’s stressful with the boss over your shoulder just wanting the database back online as soon as possible. But, with REPAIR_ALLOW_DATA_LOSS, data found to be corrupted may be deleted from the database in order to get the database back online. The range of deleted data can vary. REPAIR_ALLOW_DATA_LOSS may involve deallocating a single row or it could be multiple pages. Simply put, it’s more about getting the database up and running than fixing it back to 100%. Those are two very different results.

Are Your Backups a Better Option?

Even if your last known good backup is older than you’d like, consider this: would you rather restore an older backup and know that data after a certain date is missing or would you rather use REPAIR_ALLOW_DATA_LOSS and have to try to figure out what data is missing? It’s possible that this repair option will delete data to bring a database back up and cause more loss than knowingly sacrificing the last hour, last day, etc. There might not be a “good” or “right” decision. It may very well be a decision of “what’s least bad?”

A Last Resort

If it is still your best option or only option, you can set your database into SINGLE_USER mode, run DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS, and set your database back to MULTI_USER to see if it helps:

USE master;
GO
ALTER DATABASE [ExampleDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKDB ('ExampleDB', REPAIR_ALLOW_DATA_LOSS);
GO
ALTER DATABASE [ExampleDB] SET MULTI_USER;
GO

The point I want to get across is to not blindly run this each time you see corruption. You could be sweeping a problem under the rug that’s going to come back to bite you.

Thanks for reading!