SQL database integrity check failed means DBCC CHECKDB found corruption or could not finish and SSMS repairs nothing without a repair option. Four methods fix it. Restore a backup, use REPAIR_REBUILD, use REPAIR_ALLOW_DATA_LOSS or save the MDF objects with recovery software. REPAIR_ALLOW_DATA_LOSS can lose data and REPAIR_REBUILD does not repair FILESTREAM errors. Copy the database files first and try a backup restore before any repair command.
SQL database integrity check failed messages appear when DBCC CHECKDB finds corrupt pages or cannot finish. Databases can still open so the failure looks harmless.
Four methods fix these failures in order of risk.
What Does SQL Database Integrity Check Failed Mean?
DBCC CHECKDB checks the allocation of the database, every table, view and the catalog. Microsoft also lists indexed views and Service Broker data in the checks.
Two cases end an integrity check with a failure.
- Corruption Found. DBCC CHECKDB reports allocation errors or consistency errors in the database.
- Check Cannot Finish. DBCC CHECKDB needs the sysadmin role or the db_owner role and an internal database snapshot unless you add TABLOCK.
Healthy databases end with the message CHECKDB found zero allocation errors and zero consistency errors. Any other count means corruption.
Microsoft says the Check Database Integrity task executes DBCC CHECKDB and reports problems for an administrator or database owner to address. Repairs need a separate command with a repair option.
How to Execute an Integrity Check in SSMS?
Two free ways start the check in SSMS. One way uses a T-SQL command and the other uses a maintenance plan.
Check With DBCC CHECKDB in SSMS (Free)
- Open SSMS and connect to your SQL Server instance.
- Right click your database in Object Explorer and select New Query.
- Enter the command below with your database name and click Execute.
DBCC CHECKDB (N'InventoryDB') WITH NO_INFOMSGS;
NO_INFOMSGS hides informational messages so only errors appear. Add PHYSICAL_ONLY for a shorter integrity check because it tests page structure, record headers and allocation consistency only.
DBCC CHECKDB (N'InventoryDB') WITH PHYSICAL_ONLY, NO_INFOMSGS;
Check With the Check Database Integrity Task (Free)
- Expand Management in Object Explorer, right click Maintenance Plans and select New Maintenance Plan.
- Drag Check Database Integrity Task from the Toolbox to the design surface.
- Double click the task and select your connection.
- Select All databases, All system databases, All user databases or These specific databases in the Databases list.
- Set the options below and click View T-SQL to see the DBCC CHECKDB command.
- Click OK, save the plan and execute it.
- Include Indexes. The task checks all index pages and table data pages.
- Physical Only. The task checks page structure and allocation only and Microsoft recommends it for frequent use on production systems.
- Tablock. The task uses table locking instead of an internal database snapshot and lowers concurrency during the check.
What Schedule Fits the Integrity Check?
Microsoft says the schedule depends on your business and your production environment. Start a full check on a schedule you set and add PHYSICAL_ONLY checks between full checks on busy servers.
What Causes SQL Database Integrity Check Failed Errors?
Six reasons that can cause SQL integrity checks to fail:
- Failing Storage. Failing disks, firmware problems and faulty device drivers return pages that SQL Server finds invalid.
- File System Errors. File system inconsistencies damage the database file on the disk.
- Filter Drivers. Microsoft advises you to exclude database and log files from antivirus scanning and to test filter drivers while you troubleshoot error 824.
- Damaged Database Files. Corrupt MDF and NDF files return errors in tables, indexes and allocation pages.
- Missing Permissions. Accounts outside the sysadmin role and the db_owner role cannot execute DBCC CHECKDB.
- Snapshot Failure. DBCC CHECKDB fails against the master database when SQL Server cannot create an internal snapshot.
What to Check After SQL Database Integrity Check Failed?
Prepare the files and the evidence first.
- Make a copy of the MDF, NDF and LDF files in another folder.
- Save the full DBCC CHECKDB output because it names the objects, the pages and the lowest repair level.
- Open the Windows System event log and look for disk and driver errors.
- Find your last good full backup and the log backups after it.
- Execute SQLIOSim on the affected disk to test the I/O path.
List the pages that raised error 823 or error 824 with the query below. Only 1,000 rows fit in the table and SQL Server stops logging new pages when it is full so delete old rows.
SELECT * FROM msdb.dbo.suspect_pages
WHERE event_type IN (1, 2, 3);
Confirm that page verification uses checksums with the next query.
SELECT name, page_verify_option_desc
FROM sys.databases
WHERE page_verify_option_desc <> 'CHECKSUM';
Microsoft lists hardware and I/O path fixes as the first step after an integrity check reports errors. Check your disks first.
Which Free Methods Fix Failed Integrity Checks in SQL Server?
Three free methods repair the errors that DBCC CHECKDB reports. Methods below start with the safest and end with the riskiest.
Restore a Good Backup (Free, Safest)
Microsoft recommends a restore from a backup instead of a REPAIR option.
- Verify the backup file with the command below.
RESTORE VERIFYONLY FROM DISK = N'F:\Backups\InventoryDB.bak';
- Restore the full backup with NORECOVERY and apply the log backups after it. Use RECOVERY on the last log backup.
RESTORE DATABASE [InventoryDB]
FROM DISK = N'F:\Backups\InventoryDB.bak'
WITH REPLACE, NORECOVERY;
RESTORE LOG [InventoryDB]
FROM DISK = N'F:\Backups\InventoryDB_log.trn'
WITH RECOVERY;
- Start a new integrity check on the restored database to confirm the result.
Restored databases return data up to the last log backup only.
REPAIR_REBUILD (Free, No Data Loss)
Microsoft notes that REPAIR_REBUILD performs repairs with no possibility of data loss. Examples are missing rows in nonclustered indexes and index rebuilds. REPAIR_REBUILD does not repair FILESTREAM errors.
- Execute DBCC CHECKDB without a repair option and find the repair level in the output.
- Continue when the message names REPAIR_REBUILD as the minimum level.
- Copy the database files and execute the script below. Microsoft requires single user mode for repair options.
ALTER DATABASE [InventoryDB] SET SINGLE_USER WITH ROLLBACK AFTER 60 SECONDS;
DBCC CHECKDB (N'InventoryDB', REPAIR_REBUILD);
ALTER DATABASE [InventoryDB] SET MULTI_USER;
- Start another integrity check without a repair option to confirm that no errors remain.
Our Missing Indexes in SQL Server guide explains how to find lost indexes.
REPAIR_ALLOW_DATA_LOSS (Free, Last Resort)
Repairs with REPAIR_ALLOW_DATA_LOSS can cause some data loss according to Microsoft. Use the option only when you have no backup to restore.
- Create a full backup of the damaged database before the repair.
- Execute the commands below. Wrapping the repair in a transaction gives you a way to roll back a result you reject.
ALTER DATABASE [InventoryDB] SET SINGLE_USER WITH ROLLBACK AFTER 60 SECONDS;
BEGIN TRANSACTION;
DBCC CHECKDB (N'InventoryDB', REPAIR_ALLOW_DATA_LOSS);
-- Check the output and then execute COMMIT TRANSACTION or ROLLBACK TRANSACTION
- Set the database to multi user mode after the transaction ends.
ALTER DATABASE [InventoryDB] SET MULTI_USER;
- Execute
DBCC CHECKCONSTRAINTSand check your data because repair operations ignore constraints.Microsoft says the minimum repair level is the lowest level to attempt and not a promise that SQL Server fixes every error.
Databases in SUSPECT status need emergency mode before the repair. Our Fix SQL Database Error 1813 guide shows the emergency repair steps.
How to Fix a Failed SQL Database Integrity Check With EmailDoctor Software?
Select EmailDoctor SQL Database Recovery when no backup exists or a repair fails. Software repairs corrupt MDF and NDF files and exports the objects to a script, a CSV file or a SQL Server database.
Free version previews all recovered components and exports 25 records per table plus 5 other database objects.
Recovery software adds six features for damaged databases.
- Scan Modes. Select Quick Scan for a lightly damaged file and Advanced Scan for severe corruption.
- Suspect Databases. Recover objects from a database that SQL Server marks as suspect.
- Version Detection. Auto detect finds the SQL Server version of the MDF file.
- Saved Scans. Save a scan in an STR file and open it later without a new scan.
- Selective Export. Export all tables and objects or only the ones you need.
- Deleted Records. Red text marks deleted records in the preview.
Check the requirements below before you install the software.
- Windows. Windows 7, 8, 10 or 11 in 32 bit or 64 bit and Windows Server 2008, 2012 R2 or 2016.
- .NET Framework. Version 4.5 or higher must exist on the computer.
- SQL Server Files. Files from SQL Server 2000 to 2019 in MDF or NDF format.
- Hard Disk. 100 MB of space.
- Processor and Memory. A 1 GHz processor and 4 GB of RAM are recommended.
Scan the Damaged MDF File
- Right click the software icon and select Run as Administrator on Windows 11, 10, 8, 7 and the Server editions.
- Click the Open button and browse to your damaged MDF file.
- Choose Quick Scan for lightly damaged files. Select Auto detect for automatic version detection or choose a version from the list.
- Switch to the NDF Options tab. Choose the first option for automatic NDF detection or choose the second option to add NDF files with the Add Files and Add Folder buttons. Click OK.
- Wait until the scan finishes and the scan report appears.
- Click OK on the note that follows the scan.
Preview the Recovered Objects
- Open the folders in the left panel for functions, triggers, rules, stored procedures, views and tables. Click a table to see its records.
Export the Objects to a SQL Script
- Click Export in the top toolbar.
- Select SQL Server Compatible SQL Scripts under Export To/As.
- Tick the database components you need.
- Select With Schema & Data and click Save.
- Select a folder for the SQL files and click OK.
- Click OK when the Export Completed message appears and click Close on the status window.
- Open the folder to see the saved SQL files. Open the files in SSMS and execute them on your SQL Server to rebuild the objects.
- Execute DBCC CHECKDB on the rebuilt database to confirm a clean integrity check.
Select SQL Server Database in the export window to create a new database in one step or select CSV File Format to export table records. Select Advanced Scan when the preview shows fewer tables than your database held. Open our Repair Corrupt SQL MDF File article to see when Advanced Scan is the better choice.
Which Method Fixes SQL Database Integrity Check Failed?
Compare the four methods before you start.
| Feature | Backup Restore | REPAIR_REBUILD | Data Loss Repair | Recovery Software |
|---|---|---|---|---|
| Backup required | Yes | No | No | No |
| Fixes | Any damage up to the backup time | Index errors and other safe repairs | Errors that need the highest repair level | Severely damaged MDF files |
| Output | Restored database | Repaired database | Repaired database | Script or CSV file or new database |
| Biggest risk | Loses data saved after the backup | Severe errors remain | Loses data | Demo exports 25 records per table |
| Price | Free | Free | Free | Paid license after a free demo |
Restore the backup first. Use REPAIR_REBUILD when the DBCC output names it as the lowest repair level and use the data loss repair after that. Recovery software suits databases that no free method fixes.
What Problems Appear While You Repair the Database?
Expect the problems below during the repair.
- Permission Denied. Connect with an account in the sysadmin role or the db_owner role of the database.
- Repair Needs Single User Mode. Set the database to SINGLE_USER before you add a REPAIR option.
- Errors Vanish in the Next Check. Microsoft treats errors that appear in one integrity check and vanish in the next as possible disk cache or I/O path problems.
- Foreign Key Orphans Appear. Execute DBCC CHECKCONSTRAINTS and check your data because repair operations ignore constraints.
- Snapshot Files Remain. Files named _MSSQL_DBCC remain after an unexpected shutdown so delete them once no DBCC CHECKDB is executing.
- Invalid Column Values. DATA_PURITY reports column values that are not valid or out of range and repair options cannot fix them.
Our SQL Server Database Corruption guide lists more causes of damaged databases.
Frequently Asked Questions
Does SQL Database Integrity Check Failed Mean the Database is Corrupt?
No. DBCC CHECKDB reports corruption through allocation errors or consistency errors. Missing permissions and snapshot failures also end a check with a failure.
Does DBCC CHECKDB Repair the Errors?
No. DBCC CHECKDB reports the errors and a REPAIR option repairs them.
Which Schedule Fits Database Integrity Checks?
Microsoft says the schedule depends on your business and your production environment. Use PHYSICAL_ONLY for frequent integrity checks on production systems.
Does DBCC CHECKDB Need Exclusive Access?
No. DBCC CHECKDB uses an internal database snapshot for transactional consistency. DBCC CHECKDB falls back to locking only when SQL Server cannot create a snapshot or you add TABLOCK.
Can REPAIR_REBUILD Lose Data?
No. Microsoft notes that REPAIR_REBUILD performs repairs with no possibility of data loss. REPAIR_REBUILD cannot fix severe errors.
Does the Data Loss Option Fix Every Error?
No. DBCC CHECKDB names that option as the minimum repair level to attempt and does not promise a fix for every error. Execute DBCC CHECKDB again to confirm the result.
Can I Fix the Errors Without a Backup?
Yes. Use REPAIR_REBUILD or a last resort repair or recovery software. Restoring a backup remains the safest fix.
Which Permission Does DBCC CHECKDB Need?
DBCC CHECKDB needs membership in the sysadmin fixed server role or the db_owner fixed database role.
Can the Software Recover a Suspect Database?
Yes. Software scans the MDF file of a suspect database and recovers its objects.
Can I Export Only Selected Tables?
Yes. Select only the tables and objects you need before the export and the software exports only those items.
Is the Free Demo Version of the Software Limited?
Yes. Demo version of the software previews all recovered components but exports only 25 records per table plus 5 other database objects.
Final Words
SQL database integrity check failed means DBCC CHECKDB found corruption or could not finish. Check the output first because it names the case and the lowest repair level.
Copy the database files before a repair. Try a backup restore before any repair command.
EmailDoctor software scans a damaged MDF file and exports the recovered items to a script or a CSV file or a SQL Server database.
- Failed SQL integrity checks mean DBCC CHECKDB found corruption or could not finish.
- DBCC CHECKDB reports errors and a REPAIR option repairs them.
- Backup restores are the safest fix while the data loss option can lose data.
- Recovery software saves the recovered items as a script or a CSV file or in a SQL Server database.
Next Read: How to Open MDF File
Also Read: Repair Corrupt SQL MDF File | Export Data From MS SQL Server to CSV