Overview

The database protection and recovery is a vital role of database administrators. While working on SQL Server, sometimes users get stuck in annoying situations when the database becomes inaccessible. After executing DBCC CHECKDB command to check the problem, the user gets database consistency errors

Many companies may have the budget to have a database recovery solutions in place to protect the system from various disasters.

I would also like to discuss the third party tool Stellar Phoenix SQL Database Repair software from Stellar Data Recovery as a recovery solution.

The DBCC CHECKDB Consistency Errors behind the doors

The reason behind the database consistency error can be varied from

1. file system corruption
2. corrupted pages in memory
3. underlying the hardware system issues
4. drive issues
5. 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 the logical consistency of the database pages, rows allocation pages, system table referential integrity, index relationship, and other structure. If any of these checks fail then, errors will be described as part of the command. To resolve the inconsistency errors, users first need to restore the data from the backup of data. However, if users are not able to restore from backup then CHECKDB gives a feature to repair errors. Many times, the problem arises due to file system or hardware issue so the 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 the 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, the user can use the chdsk command. This command helps to create and displays a status report for the 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 in Logical and Physical Disk. The physical Disk performance object consists of counters that monitor hard or fixed disk on the 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 the 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. The 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 running any hardware or system checks to determine if CPU, memory or other hardware related issues exist. The user can update all system drivers, Operating system, or hardware is required on the system.

Minimum Repair Option

When the 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. The repair must be run to determine if there is the 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.

Third Party tools

To download the SQL recovery tool and test with the free Demo version of the software and then proceed.

I searched online for this product, conducted a thorough research on software and decided to download its free version.

This is how to proceed:

Check Software Specifications

Checked with software specification of Stellar Phoenix SQL Database Repair software:

Install Software on the system

• Download the software here 
• After the download, run the Setup Wizard
• Accept the License agreement
• Specify the target location for the installation
• Click Next
• Stellar Phoenix SQL Database Repair software was successfully installed and ready to use.

Test software on the system

Open the software Stellar Phoenix SQL Database Repair Software.The software GUI interface is simple and self-explanatory to perform the proceeding steps.

• Click on Select Database
• Locate the file using find database submenu. The MDF-extension related files opened and prompted for selection of the correct file.
• Next, the Stellar software scanned through MDF file and provided the scanned preview of repaired SQL Database for verification.

• Proceeded with SQL database verification and saving file at preferred location.

• Software provided various saving options and my preference rested on MDF format.

SQL Database was saved as MDF file in original format.

In a nutshell, the whole process of Database recovery and resolution of Suspect mode problem was completed with the click of three buttons –

Conclusion

In the above discussion, the problem and cause of the consistency error are 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.

The primary responsibility of the database administration team is to review all types of RDBMSs in the enterprise and to develop a comprehensive backup plan to conduct effective backup management by proactively monitoring backups, getting alerted for failed backups and rerunning these seamlessly, without loss of time. It is good practice to back up data to physical disk and to then archive the data to tape for disaster recovery purposes.

Stellar Phoenix SQL Database Repair software is a third party tool and dissolves any kind of Database related distress for SQL Administrator. I would recommend trying this tool.