DBCC CHECKDB Repair Allow Data Loss Not Working? 5 Fixes

Which Repair Options Exist in DBCC CHECKDB?

Microsoft defines REPAIR_ALLOW_DATA_LOSS as an emergency last resort. DBCC CHECKDB tries to repair all reported errors and the repairs can cause some data loss.

Two other options exist and both behave differently.

• REPAIR_FAST. Microsoft retains the option for backward compatibility and it performs no repair actions.
• REPAIR_REBUILD. Repairs have no possibility of data loss for problems like missing rows in nonclustered indexes but FILESTREAM errors need another option.

Why Does the DBCC CHECKDB Repair Option Fail?

Six reasons stop the repair or end with errors still present.

• No Single User Mode. Repair options need the database in single user mode.
• Missing Permissions. Only sysadmin and db_owner members can execute DBCC CHECKDB.
• Wrong Option. REPAIR_FAST does nothing and REPAIR_REBUILD cannot fix errors that need data removal.
• Unrepairable Error Type. DATA_PURITY errors and memory optimized tables have no DBCC repair.
• Metadata Corruption. Error 8930 ends the command before any repair starts.
• Rolled Back Repair. A rollback of the transaction brings back every error.

How to Check the DBCC Output?

Execute the check without a repair option first. Microsoft recommends this step to learn the repair level.

DBCC CHECKDB (N'OrdersDB') WITH NO_INFOMSGS, ALL_ERRORMSGS;

Finished checks end with the number of allocation errors and consistency errors. Clean checks log CHECKDB finished without errors in the SQL Server error log.

SQL Server logs a state value from 0 to 5 when the command stops early. State 0 means error 8930 raised a metadata corruption and ended DBCC.

What to Check Before You Retry?

Protect the files before you retry any repair.

• Backup. Save copies of every database file (MDF, NDF and LDF) on another disk.
• Login. Sign in as a sysadmin member or as db_owner of the database.
• Users. Close the application connections to the database.
• Disk. Check free space for the database snapshot and the log.
• Restore Point. Note which full backup and which log backups you can restore.

Which Free Fixes Work When the Repair Fails?

Four free fixes match the causes above. Try them in the order listed.

Switch to Single User Mode and Repair (Free)

Repair options need exclusive access. Execute the commands below as a sysadmin.

ALTER DATABASE [OrdersDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
SELECT user_access_desc FROM sys.databases WHERE name = N'OrdersDB';
DBCC CHECKDB (N'OrdersDB', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
ALTER DATABASE [OrdersDB] SET MULTI_USER;

ROLLBACK IMMEDIATE disconnects other sessions and rolls back their work. Execute the check without a repair option again to confirm zero errors.

Microsoft recommends a transaction around the repair so you can reject the result.

BEGIN TRANSACTION;
DBCC CHECKDB (N'OrdersDB', REPAIR_ALLOW_DATA_LOSS);
COMMIT TRANSACTION;

Execute ROLLBACK TRANSACTION instead of COMMIT when you reject the result. Errors remain after a rollback and the database then needs a restore.

Use Emergency Mode for Unopened Databases (Free)

SUSPECT and RECOVERY_PENDING databases cannot take the standard repair. Set EMERGENCY mode first.

ALTER DATABASE [OrdersDB] SET EMERGENCY;
ALTER DATABASE [OrdersDB] SET SINGLE_USER;
DBCC CHECKDB (N'OrdersDB', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
ALTER DATABASE [OrdersDB] SET MULTI_USER;

Microsoft states that in this mode DBCC CHECKDB uses pages marked inaccessible after I/O or checksum failures and acts as if those errors never happened. DBCC rebuilds the transaction log when log based recovery fails. Success sets the database ONLINE in a physically consistent state but transactional inconsistencies can remain.

In emergency mode you cannot roll back the repair with a user transaction. Microsoft states that a failed command in this mode means the database cannot be repaired.

Open our Suspect Database Fix guide for the full emergency steps.

Match the Fix to the Error Type (Free)

Some errors need a different action than the repair option.

• DATA_PURITY Errors. DBCC repair options cannot fix them. Microsoft documents manual steps on its MSSQLSERVER_2570 page.
• FILESTREAM Errors. Only the data loss option repairs them and it deletes table rows that lack file system data.
• Memory Optimized Tables. No repair option exists so you restore the last good backup.
• Constraint Violations. Execute DBCC CHECKCONSTRAINTS after every repair because the repair ignores foreign key constraints.

Take a full backup right after a successful repair.

Restore a Known Good Backup (Free, Safest)

Restores avoid data loss beyond the backup age. Use the T-SQL example below for a full backup.

RESTORE DATABASE [OrdersDB]
FROM DISK = N'D:\SQLBackups\OrdersDB_2026.bak'
WITH REPLACE, STATS = 10;

Add the log backups with RESTORE LOG when you need data up to the failure. See the BAK File Restore Guide for the SSMS wizard.

How to Recover Data After a Failed DBCC Repair With EmailDoctor Software?

Reach for EmailDoctor SQL Database Recovery when the repair fails and no backup exists. Software opens the damaged MDF file with the NDF files and shows the recovered objects in a preview.

Free demo versions preview all objects. Exports in demo mode are capped at 25 table records and 5 further objects.

Windows 7 through 11 and Windows Server 2008 through 2016 are compatible when .NET Framework 4.5 or a later version is installed. Reserve 100 MB of disk space.

Work on a copy of the MDF file. Do not point the software at the file that SQL Server uses.

Scan the Damaged File With Advanced Scan

1. Launch the software with the Start menu shortcut.
2. Click Open and browse to the copied MDF file.
3. Choose Advanced Scan because a failed repair points to heavy damage.
4. Disable Auto detect and choose your SQL Server version in the dropdown if you know it.
5. Open the NDF Options tab and choose automatic detection or manual addition of the secondary files. Click OK.
6. Wait for the scan to finish and check the scan report.
7. When the save prompt appears click Yes and store the scan in an STR file. Open that file later to avoid a new scan.

Preview and Export to a SQL Script

8. Expand the tree on the left and select a table to see its records.
9. Click Export in the toolbar.
10. Select SQL Server Compatible SQL Scripts as the format.
11. Select the objects you want to save.
12. Set the option to With Schema & Data and click Save.
13. Select a folder for the script files and click OK.
14. Confirm the Export Completed dialog and press Close on the status window.
15. Open the folder to find the saved SQL files. Review the script in SSMS before you execute it.

Which Fix Matches Your DBCC Result?

Match the symptom to the fix before you start.

Which Snags Slow Down the Repair?

Use this list when a fix does not work.

• Command Waits Forever. Add WITH ROLLBACK IMMEDIATE to the SINGLE_USER command because other sessions use the database.
• Access Denied. Sign in with a sysadmin or db_owner login.
• Errors Return Later. Check the disk and storage hardware because corruption repeats on faulty hardware.
• Foreign Key Errors Appear. Execute DBCC CHECKCONSTRAINTS and fix the orphan rows.
• Emergency Repair Fails. Microsoft calls the database unrepairable then so use a backup or recovery software.

Frequently Asked Questions

Final Words

Aggressive repair fails because of missing single user mode or permissions and because some errors have no repair option at all. DBCC output and the error log name the case.

Restore a backup whenever you have one and treat the repair as the last free attempt. Always take a fresh backup after a successful repair.

EmailDoctor software extracts the objects of a damaged MDF file into SQL scripts.