The DBCC CHECKDB command is used to check the consistency of the MS SQL Server database both physically and logically. When you execute this command, it thoroughly scans the entire database, including all tables, indexes, relationships, and database page order. After scanning, it displays a message indicating the number of database consistency errors found, repaired errors’ count, and further action required. Here is an example of the error message:

DBCC CHECKDB (mydb) executed by MYDOMAIN\theuser found 15 errors and repaired 3 errors.

Elapsed time: 0 hours 0 minutes 0 seconds.

Internal database snapshot has split point LSN = 00000026:0000089d:0001 and first LSN = 00000026:0000089c:0001.

This is an informational message only. No user action is required.

The consistency errors in SQL Server database can occur due to different reasons. Let’s find out the reasons for such errors and see how to fix them.

How to Find the Root Cause of Consistency Errors in SQL Database?

To resolve the consistency errors, it is important to know the cause behind such errors. To determine what has caused the consistency errors in SQL database, you can do the following.

Check the Windows System Event Log

The Windows System Event Log contains detailed information regarding all the actions on the server. You can check this log report to find if any disk I/O problems, driver issues, or hardware-related issues are causing the errors. In this case, you can contact your hardware manufacturer to fix the problems.

Run SQLIOSim Utility to Test Disk I/O Integrity

Sometimes, the consistency errors in SQL database can occur due to integrity issues in disk I/O. To determine this, you can simulate SQL Server activity on the subsystem of the disk. For this, you can run the SQLIOSim utility to perform integrity tests on the disk where the SQL Server database is located. You can find this utility in the \MSSQL\Binn folder.

Check the SQL Server ErrorLog File

The consistency errors can also occur if the database page is not modified according to the SQL engine code. You can check the ErrorLog file and look for the 832 errors. If you found the error 832, it means that the pages are damaged.

How to Fix DBCC CHECKDB Consistency Errors in SQL Server Database?

Mostly, the DBCC CHECKDB consistency errors occur, if there is an issue with the database or the database is corrupted. In case of corruption in the SQL database, you can follow the below steps to repair SQL database.

Method 1: Restore Database from Backup

In case of database corruption, the first thing you can try is to restore the database from backup. If you’ve a recent healthy backup, then follow the below steps to restore the database from backup:

• Open SQL Server Management Studio (SSMS) and connect to your SQL Server instance.
• Go to the left sidebar, under Object Explorer, right-click on the database node, and then select Restore Database.

• In the Restore Database dialog box, select Device and then click on ellipses (…) to select the backup file.
• Click the Add option on the Select backup devices window.

• In the Locate Backup file dialog box, go to the backup file (.BAK) and then click OK.

• Close the Select backup devices window by clicking on the OK option.
• Then, click OK to restore the database backup.

Alternatively, you can restore the database backup using the Transact-SQL commands. Here’s how:

• Launch SSMS and connect to the database engine.
• Click New Query from the standard bar.
• Now type the below SQL command:

RESTORE DATABASE mon

FROM DISK = ‘Z:\SQLServerBackups\mon.bak’ ;

Here, “mon” is the name of the database and ‘Z:\SQLServerBackups\mon.bak’ is the location of the backup in the disk.

If the backup file is corrupted or is not up-to-date, follow the next method.

Method 2: Use DBCC CHECKDB Command to Repair SQL Database

You can use the DBCC CHECKDB command to repair SQL database. Before using this command, you must have the admin rights and your database must be in single-mode. 

To set the database in SINGLE_USER mode, you can use the below command:

ALTER DATABASE mon SET SINGLE_USER 

Here, ‘mon’ is the name of the database.

Once the database is in single user mode, you can use the DBCC CHECKDB command with REPAIR_REBUILD option (see below):

DBCC CHECKDB (‘mon’, REPAIR_REBUILD)

GO

This command will rebuild the database without any data loss. It can repair missing rows in non-clustered indexes. However, it is a time-consuming option.

Alternatively, you can use the following command for fast repair:

DBCC CHECKDB (‘mon’, REPAIR_FAST)

GO

This quick repair option only maintains backward compatibility syntax. It does not perform any repair actions.

If the above commands fail to repair the database, then you need to run the DBCC CHECKDB command with the REPAIR_ALLOW_DATA_LOSS option (see the below example):

DBCC CHECKDB (N ’mon’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS; 

GO

After repairing the database, set it back to MULTI_USER mode by using the below command.

ALTER DATABASE mon SET MULTI_USER

The REPAIR_ALLOW_DATA_LOSS can result in data loss. To prevent data loss, you can use a professional SQL database repair tool, such as Stellar Repair for MS SQL. It can repair corrupted SQL database (MDF and NDF) files and recover all the objects, such as triggers, tables, indexes, stored procedures, and others with complete integrity. You can free download the demo version of the tool to preview all the recoverable items in the database and evaluate the functionality of the tool.

Conclusion

The DBCC CHECKDB consistency errors can occur due to several reasons. First, identify the cause of the error using the Windows System Event Log, Error log file etc., as mentioned in the article. If the consistency issues have occurred due to corruption in the database, then follow the methods in the article to repair the database. If the database is severely corrupted, then try a reliable SQL database repair tool, like Stellar Repair for SQL. It can repair corrupted SQL database files and recover all the data with 100% integrity.