Most Effective Tactics to Fix Database Consistency Error in SQL Server

This is the most commonly query faced by SQL users. Here we will going to discuss the solution on how to fix Database Consistency error. But first, let us discuss some keypoints this blog will cover.

• Possible reason behind Database Consistency error
• How to check Database consistency error in SQL Server
• How to Fix Consistency Error in SQL Server Database?

DBCC CHECKDB Consistency Error

CHECKDB found 0 allocation errors and 4 consistency errors in database 'TestDB’
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (TestDB).
DBCC execution completed. If DBCC printed error messages, contact your system administrator. 

How to Check Database Consistency Error in SQL Server

• Check Windows System Event Log- It will let you know the issue in system level, driver or disk related error.
• Run CHKDSK command- It will check the integrity of the file system.
• Work with Hardware Vendor – It will ensure you whether the configuration of hardware devices or supporting software components are updated or not.
• Try SQLIOSim  - It is an independent tool which test the integrity check of I/O disk system.
• Check Access Violations and Assertion for any SQL error.
• Make sure that uour database is using PAGE_VERIFY CHECKSUM option. If checksum error is being reported then your disk subsystem is thoroughly checked.
• Check error logs if it indicates SQL msg 832. It indicates the page get damaged when it is in cache.
• Restore Database backup and transaction log backup.

How to Fix Consistency Error in SQL Server Database

When DBCC CHECKDB report consistency error, the best solution is to restore it from good backup. But what if you fail to restore from backup? Then DBCC CHECKDB provides you an option to fix consistency error.But, if you are facing issue in hardware or file system, make sure to correct them before restoring or running the repair command.

Run below T-SQL command to attempt repair:

ALTER DATABASE Db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

DBCC CheckDB ('Db', REPAIR_ALLOW_DATA_LOSS)
GO 

ALTER DATABASE Db SET MULTI_USER

Important Note: The repair option is considered as the minimum level of repair to resolve errors from DBCC CHECKDB. It means, if you run repair command, that does not mean that you will fix all errors. Not all errors generated by DBCC CHECKDB require this level of repair to fix this issue. And not all repair error will result in data loss.

One option to know the repair level for each table is to run DBCC CHECKTABLE, this will show the minimum level of repair for a given table.

If the repair option does not work to repair SQL Database corruption, then the only option is to restore or repair SQL database from recent backup.

Quick Way to Fix Database Consistency Error in SQL Server

REPAIR_ALLOW_DATA_LOSS is the minimum level of repair option. It doesn’t guarantee fixing SQL errors. For that case, you can switch to the third party utility ie SQL Database Repair Tool to recover SQL Database & make the database in the consistent state. You just need to perform few steps:

Download SQL Database Recovery Purchase SQL Recovery

• Load the file by clicking on Open

• Now choose the Scan mode from Quick and Advanced Scan. Quick scan mode is for normal corruption and advanced scan mode is for major level of corruption. Auto-Detect option is there if you dont know the version of MDF file. The user can also select preview deleted records option to preview deleted SQL table records in red color.

• Click Export to save recovered data in SQL Server database or as .sql script.

Conclusion

The blog covers on how to fix consistency errors in SQL Server Database error. You will also get to know how you can you find out the cause of Database consistency Error in SQL Server. You can fix it manually by using commands or can fix by an automated approach, both described in the above blog.