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.
The REPAIR_ALLOW_DATA_LOSS option in SQL Server is a last-resort repair option used when attempting to fix corruption in a database. It is part of the DBCC CHECKDB command, which is designed to check the logical and physical integrity of a SQL Server database. As the name implies, running this repair option may result in the loss of data, as it removes corrupted portions of the database that cannot be repaired. This option is typically used only after other recovery methods, such as restoring from a backup, have been exhausted or are unavailable.
When using REPAIR_ALLOW_DATA_LOSS, SQL Server attempts to bring the database back to a consistent and usable state. It does this by removing corrupted rows, pages, or even entire tables if necessary. While this may make the database operational again, it comes at the cost of losing data that could not be reconstructed. Due to the high risk of data loss, this repair option should be approached with extreme caution. It is also important to note that databases repaired using this method may still have lingering issues, such as missing data relationships or orphaned rows, that need to be addressed manually.
Because of its risks, Microsoft recommends that REPAIR_ALLOW_DATA_LOSS only be used when restoring from backups is not an option. Before running this command, always ensure you have a copy of the corrupted database for analysis or future attempts at recovery. After running the repair, carefully review the database to identify and assess the data that was lost. For critical situations involving database corruption, working with SQL Server professionals who specialize in database repair can help mitigate risks and explore alternative recovery options before resorting to this repair mode.
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; 
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; 
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