If you have heard me present on database corruption and repair, you’ve likely heard me vent about how damaging REPAIR_ALLOW_DATA_LOSS can be.

Please schedule a free 30 min consult BEFORE your attempt to repair a corrupt SQL Server database using this method.

If you run repair_allow_data_loss you essentially throw away part or all of your data making it much much harder to repair = more time and money spent! Contact us!

Lets look at an example here for a simple database that has a table called [Revenue] that has corruption in it. In this very simple example there are only 54 rows in this table and those 54 rows are stored in 2 pages (8k allocation chunks) in SQL Server. One of the two pages is corrupt.

DBCC CHECKDB('CorruptionChallenge1') WITH NO_INFOMSGS;

We can see from the output that the corruption is in index ID of 1 which is the clustered index on the table. Not clustered indexes have ID’s greater than 1.

In a blog post at SteveStedman.com I recently posted on how you should not use REPAIR_ALLOW_DATA_LOSS and the dangers of using REPAIR_ALLOW_DATA_LOSS.

But lets take a look at an example of REPAIR_ALLOW_DATA_LOSS and what that does to your database. What repair allow data loss does is it throws away any corrupt pages, and of course any rows on those corrupt pages.

Since the table has 2 data pages, and one of the is corrupt what it will do is throw away half of the data in this table.

SELECT COUNT(*), sum(Revenue) as TotalReveune 
FROM Revenue;
54 rows and about $16 million in revenue.
use CorruptionChallenge1;
go
ALTER DATABASE CorruptionChallenge1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DBCC CHECKDB('CorruptionChallenge1', REPAIR_ALLOW_DATA_LOSS) ;

ALTER DATABASE CorruptionChallenge1 SET MULTI_USER;

SELECT COUNT(*), sum(Revenue) as TotalReveune FROM Revenue;

The code above does indeed get rid of the corruption, however, SQL Server lies to you when it says “The error has been repaired”. I would expect from a statement like this to mean the corruption was repaired, but that is not the case. The repair in this case means the SQL Server threw away the page with the corruption on it. It didn’t repair anything it just threw out the data.

We can see that it threw out the data by running the query to see how many rows and how much data we have in the revenue table.

SELECT COUNT(*), sum(Revenue) as TotalReveune 
FROM Revenue;
27 rows and about $5 million in revenue

Half of the rows are gone and just over $10 million in revenue is missing. I would not call that repaired. That’s like going to the doctor for a broken finger, cutting off your hand, and saying you’re all good, the broken finger has been fixed.

I stress this again that you should never run repair allow data loss without someway to get the data that it destroys back.

Before running repair_allow_data_loss, please reach out to us!

>>> You could save yourself time and money! Schedule Your FREE 30 min consult!

We can provide options to repair you database that don’t involve throwing your data away. Schedule Consult Now

-Steve Stedman

Contact Info

Stedman Solutions, LLC.
PO Box 3175
Ferndale WA 98248

Phone: (360)610-7833

Similar Posts