Overview

While working on SQL Server, sometimes users get stuck in annoying situations when database become inaccessible. After executing DBCC CHECKDB command to check the problem, user gets database consistency errors.

Causes Behind DBCC CHECKDB Consistency Errors

The reason behind the database consistency error can be varied from file system corruption, corrupted pages in memory, underlying the hardware system issues, drive issues, or some problem with SQL Server Engine.

How to Fix Database Consistency Errors Reported By DBCC CHECKDB ?

DBCC CHECKDB checks the physical as well as logical consistency of the database pages, rows allocation pages, system table referential integrity, index relationship, and other structure. If any of these checks fails then, errors will be described as part of command. To resolve the inconsistency errors, users first need restore the data from the backup of data. However, if users are not able restore from backup then CHECKDB gives a feature to repair errors. Many times, the problem arises due to file system or hardware issue so user should correct these first before restoring and running repair. There are some resolutions that discussed below to help users to fix SQL database consistency errors.

Windows System Event Log

Windows System Event Log errors indicate possible I/O problems that can be associated with inconsistency of database. It contains various Event IDs depend upon the inaccessibility of data. Every event ID has different resolution to fix the occurrence of an error.

Integrity of File System

Many times, the users face the inconsistency error due to the file system integration. To check the integrity of file system, user can use the chdsk command. This command helps to create and displays a status report for disk. It also lists all the errors on the disks. It is available with different parameters from the command prompt.

I/O Requirements

There are various counters, which are related to I/O and all are located under Logical and Physical Disk. The physical Disk performance object consists of counters that monitors hard or fixed disk on system. The logical disk performance of an object consists of counters, which monitors the logical portion of fixed or hard disk drives.

SQLIOSim Utility

There is a utility known as SQLIOSim that also reports the consistency errors. SQLIOSim is a tool, which is independent of SQL Server Engine for testing the integrity of I/O for the disk system. It can be downloaded from the web for utilization.

Note: SQLIOSim ships on SQL Server 2008 does not require separate download.

Verify Checksum Option

Make sure that the database, which is using PAGE_VERIFY CHECKSUM option. If the checksum error is reported then, it indicates that the consistency error has occurred. After writing the SQL Server, pages to disk the error occurred so that the disk system should be checked thoroughly. User gets 824 error in SQL Server, which is caused due to some external conditions. Some external conditions have caused the modification on database page outside the SQL Server engine code. It can be resolved if the user by running any hardware or system checks to determine if CPU, memory or other hardware related issues exist. User can update all system drivers, Operating system, or hardware if required on the system.

Minimum Repair Option

When user runs DBCC CHECKDB, a recommendation is used to indicate the minimum repair option, which is essential to repair all errors. Its message appears as mentioned below:
CHECKDB found 0 allocation errors and 15 consistency errors in database 'adventureworks'.

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (adventureworks

The repair recommendation is the minimum level of repair to attempt to resolve all the errors from CHECKDB. It does not mean that all the errors are fixed by repair option. It means that all the errors that are not reported by CHECKDB. When repair_allow_data_loss is recommended will result in loss of data. Repair must be run to determine if there is resolution of an error that will result in data loss. It is a technique to help narrow down if the repair level for each table is to access DBCC CHECKTABLE for any table that is reporting an error. It will display the minimum level of repair for given table.

Conclusion

In the above discussion, the problem and cause of the consistency error is described. Along with this, solution on to how to troubleshoot database consistency errors reported by DBCC CHECKB is discussed. It makes easy for users the error occurrence.