repair_rebuild vs repair_allow_data

Yesterday I posted about how repair_allow_data_loss should never be used with CHECKDB or CHECKTABLE. Today I want to share the difference between repair_rebuild vs repair_allow_data_loss.

DBCC CHECKDB is a critical tool in SQL Server for checking the consistency of a database. When corruption is detected, it offers repair options: REPAIR_REBUILD and REPAIR_ALLOW_DATA_LOSS. Each serves distinct purposes and has significant differences. Here’s an in-depth look:

First off, after reading the post about never using repair_allow_data_loss, and you will understand that repair_allow_data_loss simply throws away pages.

Next what repair_rebuild attempts to do is to rebuild pages in non-clustered indexes that are corrupt. I have never seen this work for clustered indexes.

Repair_rebuild – will do no harm however if doesn’t help very often, but repair_allow_data_loss will throw away data.

Here is an example, using the week 1 database from the Database Corruption Challenge, however this is just showing how data is lost when you use repair_allow_data_loss with checkdb.

First we are just going to restore the Week1 database after downloading it from the Corruption Challenge.

RESTORE DATABASE [CorruptionChallenge1] FROM  
DISK = N'C:\presentations\DataBase Coruption Challenge\Week1\CorruptionChallenge1.bak' WITH  FILE = 1,  
MOVE N'CorruptionChallenge1' TO N'E:\SQL2017\Data\CorruptionChallenge1.mdf',  
MOVE N'CorruptionChallenge1_log' TO N'E:\SQL2017\Logs\CorruptionChallenge1_log.ldf',  NOUNLOAD, REPLACE,  STATS = 5;

Next we run checkDB to take a look at the problems.

use CorruptionChallenge1;
GO

DBCC CHECKDB('CorruptionChallenge1') WITH NO_INFOMSGS;

repair_rebuild vs repair_allow_data_loss

We can see the corruption in the Revenue table, so we take a look at how many rows we have and how much revenue has been recorded in that table.

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

You can see, 54 rows in the table with $16,283,040 recorded in revenue. Imagine if this was the revenue for your department, not bad depending on what you might be selling.

Next we try checkdb with repair_rebuild.

use CorruptionChallenge1;
go
ALTER DATABASE CorruptionChallenge1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB('CorruptionChallenge1', REPAIR_REBUILD) ;
ALTER DATABASE CorruptionChallenge1 SET MULTI_USER;


DBCC CHECKDB('CorruptionChallenge1') WITH NO_INFOMSGS;
SELECT COUNT(*), sum(Revenue) as TotalReveune FROM Revenue;

The repair_rebuild does nothing in this case, and our revenue table remains unchanged.

Then we try checkdb with repair allow data loss.

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;

DBCC CHECKDB('CorruptionChallenge1') WITH NO_INFOMSGS;

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

Which says that it repaired things, but the output lies. (Lies shown with the blue arrows).

Technically it didn’t repair anything, instead it just threw away data. We can see that when we check the revenue table again to see how many rows are there and how much revenue we have. Yes the corruption has been removed, but so has our data.

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

Somehow we are missing $10,901,260 in revenue. Try to explain that one to your CFO. CheckDB “fixed” the database by throwing away half of the rows in this table.

You can run checkdb with repair_allow_data_loss in a transaction to see what is going to happen then to run it back. This is safe to do in most scenarios.

use CorruptionChallenge1;
go
ALTER DATABASE CorruptionChallenge1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
BEGIN TRANSACTION 
	DBCC CHECKDB('CorruptionChallenge1', REPAIR_ALLOW_DATA_LOSS) ;

	DBCC CHECKDB('CorruptionChallenge1') WITH NO_INFOMSGS;
ROLLBACK TRANSACTION;
ALTER DATABASE CorruptionChallenge1 SET MULTI_USER;

When considering repair_rebuild vs repair_allow_data_loss, it’s essential to understand their different purposes and the risks involved. REPAIR_REBUILD is a safe, non-destructive option used for minor repairs, such as rebuilding indexes or fixing simple consistency errors. It does not modify or remove actual data, making it the preferred choice for resolving manageable corruption issues without the risk of data loss. In contrast, REPAIR_ALLOW_DATA_LOSS is a more aggressive option used for severe corruption, where data itself is damaged and cannot be repaired. This method can restore a database to a usable state but does so by removing unrecoverable data, often resulting in data loss.

The decision between repair_rebuild vs repair_allow_data_loss depends on the severity of the corruption and the availability of recent backups. For issues that can be addressed with REPAIR_REBUILD, it’s always advisable to use this safer option first. However, in cases where the corruption is critical and impacts the data itself, REPAIR_ALLOW_DATA_LOSS may be the only available solution if a backup cannot be restored. It’s important to approach this option with caution, as the resulting database will need careful review to assess and mitigate the impact of lost data.

Ultimately, the choice of repair_rebuild vs repair_allow_data_loss comes down to balancing the need for immediate database recovery with the potential for data loss. Whenever possible, restoring from a verified backup remains the best practice, as it maintains data integrity and avoids the risks associated with repair commands. For situations where repair options are necessary, working with SQL Server experts can help you determine the most effective and least disruptive path forward.

At this point I hope that repair_rebuild vs repair_allow_data_loss is no longer a question.

I still stick with my statement of never running repair allow data loss without knowing what is going to be lost, and without a way to get it back. Also most any time that you have a scenario that repair_rebuild will work, it is usually just as easy to fix an index by dropping and recreating it.

Good luck with your corruption repair, and if you need any help, please reach out to us at Stedman Solutions before ever running REPAIR_ALLOW_DATA_LOSS.

-Steve Stedman

repair_rebuild vs repair_allow_data_loss

Leave a Reply Cancel reply