Most effective strategy to resolve consistency error with DBCC CHECKDB command

Sarthak Poswal

Updated On - May 8, 2024

Databases are an integral part of any organization’s success. Organizations, whether small or large, use database management system (DBMS). They usually hold data like employee information, product details, transaction records, student records, etc. Often companies make use of this huge amount of data to perform data analysis. With this analysis, they try to find solutions to questions like how to increase profit or what new product the company should launch.

Inconsistency in a database basically means that the same field or column has different values available in more than one table.

For example – 2 tables in a database hold information about the same student. But both the tables hold different values for the column “Father’s Occupation”.

Possible reasons for inconsistency:

• File Corruption
• Hardware issue
• Page corruption in memory
• Faulty driver
• SQL server issue
• Improper shutdown of database

Verifying if database is inconsistent using dbcc checkdb command.
First thing to do in this scenario is to verify for any inconsistency. Simplest way to do so is running the query dbcc checkdb on the database facing issues.
Executing the dbcc checkdb query:

1. Set the database status to emergency mode.

ALTER DATABASE [Your_DB_Name] SET EMERGENCY

2. Execute the query:

DBCC CHECKDB (Your_DB_Name)

The above query will provide output like:

“DBCC CHECKDB (mydb) executed by MYDOMAlN\theuser found 15 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds. Internal database snapshot has split point LSN = 00000026I0000089dI0001 and first LSN =00000026:0000089c:0001.”

The dbcc checkdb command will check the integrity of the database. It will examine the physical and logical consistency of data’s different components. The errors, if any, will be reported.

Before jumping to the repair method, it is essential to determine that the issue lies within the database and not any other component of the system or server. The process of repairing is typical and can even lead to data loss.
Some common check that the user must perform before beginning with the repair process:

• Update system drivers
• Check the hard disk for bad sectors
• Update the SQL server to latest version
• Reboot the system

In this section, we are going to discuss various manual methods to resolve consistency error with DBCC CHECKDB command.

If you are having a backup of your database which is free from consistency errors, then you can simply restore the database with this backup. It’s the most simple and effective treatment you can do.
Note: The backup will replace the current database. Therefore, keep in mind that any data inserted into database post-backup will be lost permanently.
To restore the database, run the following query:

RESTORE DATABASE YourDatabaseName
FROM DISK = ‘FULL_PATH_TO_BACKUPFILE’
WITH REPLACE;

After restoring the database run the dbcc checkdb query again and verify if all the inconsistencies are gone.

When you execute the dbcc checkdb query it displays consistency errors and also mentions the minimum repair level needed to fix the issue. It can show two levels, REPAIR_REBUILD and REPAIR_ALLOW_DATA_LOSS.

The REPAIR_REBULD can fix minor inconsistencies, and most of the time the system will ask for REPAIR_ALLOW_DATA_LOSS as the minimum level.

Depending upon the output, the user can proceed with running the query:

1. Set the database to single-user mode and execute the following query

ALTER DATABASE YourDatabaseName SET SINGLE_USER

2. Run the repair query.

For minimum level repair = repair_rebuild:

DBCC CHECKDB (‘YourDatabaseName’, REPAIR_REBUILD);

3. For minimum level repair = repair_allow_data_loss:

DBCC CHECKDB (‘YourDatabaseName’, REPAIR_ALLOW_DATA_LOSS);

4. Set the database back to multi-user mode

ALTER DATABASE YourDatabaseName SET MULTI_USER

5. After the repair, run DBCC CHECKDB again to ensure that all consistency errors have been resolved.

Note: The repair allow with data loss will delete pages from the database which means data loss. So, 100% data restoration is not possible with this repair option.

It’s a tool from Microsoft for testing reliability and integrity of disk subsystems that SQL Server utilizes. It simulates read, write, checkpoint, backup, sort, and read-ahead activities. All these activities are normally performed by SQL server upon deployment.

This tool basically helps in checking the system on which the server will be deployed. This helps in eliminating any underlying hardware issue which may be creating trouble.

Location of SQLIOSim: You can locate it in the Binn folder in the root directory where you have installed the SQL server.

The manual methods discussed above are complicated and do not guarantee a solution. The user may even lose data with an inexact step while performing them.
Therefore, experts recommend using a professional tool Recoveryfix for SQL Database Recovery. This tool is developed with advanced algorithms and offers unparallel functionalities.
Why use it:

• Embedded with innovative algorithms for meticulous recovery.
• Swiftly recover objects, including tables, views, stored procedures, indexes, rules, and user-defined data types.
• Compatible with all SQL versions.
• Maintains complete data integrity and original hierarchy.

Conclusion

Consistency issues are not uncommon in SQL databases. Therefore, regular monitoring and maintaining a good, consistent backup is essential and critical. However, if you do find yourself in a situation with such issues then the above-mentioned solutions will help you. However, these manual methods may get tricky and may even lead to data loss. For a quick solution with 100% data retention Recoveryfix for SQL Database Recovery is an optimal solution. It provides an accessible user interface and offers swift operations. The SQL tool is also capable of easily restoring data from a corrupted database file.