DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS, What It Deletes

REPAIR_ALLOW_DATA_LOSS is the command people reach for when a database is corrupt and the backups are gone, and its name is the most honest thing about it. It does not repair your data in the sense most people expect. It makes the database structurally consistent by removing whatever it cannot fix, which often means deleting rows you wanted to keep. Understanding that difference before you run it is what separates a measured recovery from a self inflicted loss.

We build SQL recovery tools at Univik and have seen what this command leaves behind since 2013, databases that came back online with tables quietly lighter than they went in. Run it wrong and you lose data a restore would have saved. Run it right, and only when you must, and it brings a dead database back at a cost you chose with open eyes rather than one the command chose for you.

What REPAIR_ALLOW_DATA_LOSS Actually Does

DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS is the most aggressive repair level of SQL Server’s consistency checker. When CHECKDB finds corruption it cannot mend without discarding data, this option clears the errors by deallocating the damaged pages, a row, a page or a run of pages, whatever it takes to leave the database structurally sound. As Microsoft’s DBCC CHECKDB documentation puts it plainly, any deallocated data is no longer accessible or recoverable, and the exact contents cannot be determined.

The key word is consistent, not complete. After the command succeeds, the database is physically sound and comes back online, but it may be missing data and it may have broken relationships between tables. SQL Server does not try to preserve your rows here. It removes the corruption so the engine can trust the file again, and your data is simply what survives that process. Our guide to the MDF file format explains how SQL Server stores rows in 8 KB pages, which is the unit this command works in and the reason the loss is rarely a single row.

REPAIR_REBUILD or REPAIR_ALLOW_DATA_LOSS

DBCC CHECKDB offers two real repair levels, and the difference between them is whether your data is at risk. Running the wrong one wastes the safe option, so know which is which before you type either.

An older option, REPAIR_FAST, still exists but does nothing now, kept only so older scripts do not break. So the real choice is between the two above, and DBCC CHECKDB tells you which one the corruption needs. Run the plain command first, and it reports the minimum repair level at the bottom of its output, a line that reads that REPAIR_REBUILD or REPAIR_ALLOW_DATA_LOSS is the minimum level for the errors found. That line names the least destructive option that will work, and it is information, not an instruction to run it.

How Much Data It Deletes

This is the part that surprises people, and it is the strongest reason to treat the command with care. SQL Server checks data integrity at the page level, not the row level, because the CHECKSUM that detects corruption is calculated for the whole 8 KB page. So when a single row on a page is corrupt, REPAIR_ALLOW_DATA_LOSS does not delete that one row. It deallocates the entire page, and every good row that shared it goes too.

How much you lose depends on the corruption. A single damaged page might cost a handful of rows, while damage to an allocation structure or a clustered index can cascade into far more. And when the corruption sits in a structure that tracks where data lives, an allocation map or a higher level index, repairing it can drop references to pages that were never corrupt themselves, so a table you thought was healthy loses rows anyway. One fair point cuts the other way. REPAIR_ALLOW_DATA_LOSS being the recommended minimum level does not mean every fix it makes loses data, since some errors at that level resolve cleanly, and you only learn which by running it. That is another reason to run it inside a transaction you can roll back. Even so, the unpredictability is exactly why Microsoft, and SQL Server experts like Paul Randal in his piece on repair as the very last resort, frame the command as a last resort rather than a repair you run on a hunch. To find which table actually needs the destructive level instead of applying it across the whole database, run DBCC CHECKTABLE on each table the check flagged, since it reports the minimum repair level for that one table.

How to Run It Safely

When you have weighed the loss and have no backup to restore, this is the sequence that does the least harm. Every step matters, and the first two are the ones rushed guides skip.

-- 1. Check first, WITHOUT repairing, to read the recommended minimum level
DBCC CHECKDB (YourDatabase) WITH NO_INFOMSGS, ALL_ERRORMSGS;

-- 2. Back up the current files before any repair touches them
BACKUP DATABASE YourDatabase TO DISK = 'D:\Backups\YourDatabase_prerepair.bak'
  WITH COPY_ONLY, INIT;

-- 3. Put the database in single user mode
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- 4. Try the no data loss level first, if CHECKDB recommended it
DBCC CHECKDB (YourDatabase, REPAIR_REBUILD) WITH NO_INFOMSGS;

-- 5. Only if REPAIR_ALLOW_DATA_LOSS is the named minimum level
DBCC CHECKDB (YourDatabase, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;

-- 6. Return the database to normal use
ALTER DATABASE YourDatabase SET MULTI_USER;

The backup in step 2 is not optional. Once REPAIR_ALLOW_DATA_LOSS runs, the deleted data is gone for good, so a copy of the corrupt file is your only way back if the repair goes worse than the corruption. Microsoft recommends this backup for exactly that reason. If the corruption stops the backup from finishing, copy the physical MDF and LDF files instead, which works regardless of the damage because it copies the bytes without reading them as pages. Corruption returns when its cause is left unfixed. New errors on a second CHECKDB run usually point to a failing disk or driver, and Microsoft’s guidance on troubleshooting consistency errors is clear that repairing again without fixing the hardware only buys time.

There is a safety net Microsoft recommends that most guides never mention. Because the repair options are fully logged, you can wrap the repair in a transaction, inspect what it reports, then keep or undo it. If the repair deletes more than you can accept, a rollback puts every page back.

-- Wrap the repair so you can undo it (run in single user mode)
BEGIN TRANSACTION;
DBCC CHECKDB (YourDatabase, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;

-- Read the output. If the loss is acceptable, keep it:
COMMIT TRANSACTION;

-- If it deleted too much, undo the whole repair instead:
-- ROLLBACK TRANSACTION;

This one habit turns an irreversible command into a reversible decision, and it is the single best protection you have short of a backup. Leave the transaction open only as long as you need to read the results, since the database stays locked in single user mode until you commit or roll back.

What to do After the Repair

The command leaving the database online is not the end of the job, because a physically consistent database can still be logically broken. Deallocating pages removes rows without checking what pointed at them, so foreign key relationships can be left dangling, a child row referring to a parent that no longer exists. REPAIR_ALLOW_DATA_LOSS does not check or maintain those constraints, which is why Microsoft says to verify them yourself afterward.

-- Check the referential integrity the repair did not maintain
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS;

Run that across the database, review what it flags, and repair the business logic by hand where you can. Then back up the newly repaired database immediately, since your old backups predate the repair and the file has changed. And find out what caused the corruption in the first place, because a bad sector or a failing controller that went unaddressed will corrupt the database again. Skipping these steps is how a database comes back online looking healthy and fails an audit weeks later.

When to Skip It Entirely

Two situations call for closing the query window. The first is simple, if you have a good backup, restore it. Microsoft is explicit that REPAIR_ALLOW_DATA_LOSS is not an alternative to a restore, and a clean backup brings the database back whole where the repair brings it back lighter. Our guides on a database stuck in recovery and a database marked suspect both put restore ahead of repair for this reason.

The second is the case this command handles worst, when the data it would delete is the data you need and no backup exists. REPAIR_ALLOW_DATA_LOSS answers corruption by deletion, which is the opposite of what you want when every row counts. DBCC has hard limits of its own as well, since corruption in allocation pages, the system tables or the database header can defeat it, and emergency mode with it, leaving the command unable to run at all. A file level recovery tool takes the other path. The Univik SQL Database Recovery tool reads the MDF page by page without attaching it to SQL Server, and it pulls readable rows out of pages that DBCC would simply deallocate, so you keep the data rather than trade it for consistency. The free MDF Viewer shows what is still readable before you decide. When there is no backup at all, our guide on recovering a SQL database without a backup lays out the full set of options.

Frequently Asked Questions

Does REPAIR_ALLOW_DATA_LOSS Always Delete Data?

Not every time, but it can, and that is the point of the name. It clears corruption by deallocating damaged pages, and any data on those pages is gone for good. Some runs remove only a few rows, others remove far more, and the exact amount cannot be known in advance. Run a plain DBCC CHECKDB first to check whether the safe REPAIR_REBUILD level is enough before you reach for this one.

What is the Difference Between REPAIR_REBUILD and REPAIR_ALLOW_DATA_LOSS?

REPAIR_REBUILD fixes structural problems with no possibility of data loss, mainly rebuilding indexes and repairing missing rows in non clustered indexes. REPAIR_ALLOW_DATA_LOSS fixes what REPAIR_REBUILD cannot, but it does so by deleting corrupt pages, which loses the data on them. DBCC CHECKDB names the minimum level the corruption needs, and REPAIR_REBUILD is always the one to hope for.

How Much Data will REPAIR_ALLOW_DATA_LOSS Remove?

It depends on the corruption, and it is more than the single bad row people expect. SQL Server checks integrity per 8 KB page, so a whole page is deallocated even when only one row on it is corrupt, taking every other row on that page with it. Damage to allocation structures or shared index pages can remove far more, including rows from tables that were never corrupt themselves.

Do I Need to Back Up Before Running REPAIR_ALLOW_DATA_LOSS?

Yes. Once the repair deallocates pages, that data cannot be recovered, so a copy of the corrupt file is your only way back if the repair makes things worse. Microsoft recommends backing up before running the command. Use a COPY_ONLY backup so you preserve the file exactly as it is before the repair changes it.

What should I Run After REPAIR_ALLOW_DATA_LOSS?

Run DBCC CHECKCONSTRAINTS, because the repair deletes rows without maintaining foreign keys, so it can leave broken relationships behind. Review what it reports and fix the logical inconsistencies by hand. Then back up the repaired database, since the file has changed, and investigate the root cause, usually a disk or driver fault that will corrupt the database again if left alone.

Is There a Way to Repair Corruption Without Losing Data?

Restoring from a clean backup loses nothing and is always the first choice. When no backup exists and the data matters, a file level recovery tool such as Univik SQL Database Recovery reads the MDF directly and extracts rows from pages that REPAIR_ALLOW_DATA_LOSS would delete, which recovers data the command would discard. It is the alternative for exactly the case where losing rows is not acceptable.

The Bottom Line

REPAIR_ALLOW_DATA_LOSS is a consistency tool wearing the costume of a repair tool. It makes a corrupt database usable again by deleting what it cannot fix, and because it works in whole 8 KB pages, the loss is often wider than the corruption that triggered it. It has its place, as a genuine last resort when a database must come online and no backup exists, but it is never the first move and never a substitute for a restore.

So run the plain check first, restore from backup whenever you can, and back up the corrupt file before you let the command touch it. And when the rows it would delete are the ones you came to save, remember that deleting them is not your only option. Reading the pages and pulling the data out keeps what the command would throw away, which is the difference between a database that works and a database that still has your data in it.