How to Fix Database Consistency and Integrity Issues

Database protection and recovery is a vital role of database administrators. While working on SQL Server, sometimes users get stuck in annoying situations in which the database becomes inaccessible. After executing the 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 software tool Stellar Phoenix SQL Database Repair as a recovery solution.

The reason behind the database consistency error could be:

1. File system corruption
2. Corrupted pages in memory
3. Underlying the hardware system issues
4. Drive issues
5. A problem with SQL Server Engine

DBCC CHECKDB checks the physical as well as the logical consistency of the database pages, rows allocation pages, system table referential integrity, index relationships, and other structures. If any of these checks fail, errors will be described as part of the command. To resolve inconsistency errors, users first need to restore the data from the backup of data. However, if users are not able to restore it from the backup, CHECKDB has 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 repairs. There are some resolutions 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 may 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, users face inconsistency errors due to file system integration. To check the integrity of the 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 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 disks on the system. The logical disk performance of an object consists of counters that monitor the logical portion of fixed or hard disk drives.

SQLIOSim Utility

There is a utility known as SQLIOSim that also reports consistency errors. SQLIOSim is a tool that 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 shipped on SQL Server 2008 does not require the separate download.

Verify Checksum Option

Make sure that the database is using the PAGE_VERIFY CHECKSUM option. If the checksum error is reported, this indicates that a consistency error has occurred. After writing SQL Server pages to disk, the error occurred so that the disk system would be checked thoroughly. The user gets an 824 error in SQL Server, which is caused by external conditions that have caused the modification on the 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 systems, or hardware required by 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. This does not mean that all the errors are fixed by the repair option. It means that all the errors that are not reported by CHECKDB. When repair_allow_data_loss is recommended, this will result in a 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 whether 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.

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.

• Operating System: Windows Server 2012/2008/2003 and Windows 10/8/.1/7/Vista/XP

• Version supports: MS SQL Server 2016, 2014, 2012, 2008 R2, 2008, 2008*64, 2008 Express, 2005, 2005*64, Express, 2000, 2000*64, 7.0, and mixed format

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.
• The Stellar Phoenix SQL Database Repair software was successfully installed and is ready to use!

Test software on the system:

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

• Click Select Database.
• Locate the file using Find database submenu. The MDF-extension related files opened and prompted to select the correct file.
• Next, the Stellar software scans through MDF file and provides a preview of the repaired SQL database for verification.

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

The SQL database was saved as an MDF file in original format.

In a nutshell, the whole process of database recovery and the resolution of the suspect mode problem was completed with the click of three buttons: Select file > Repair file > Save file.

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 CHECKDB 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 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 the 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 stress for SQL administrator. I would recommend trying this tool.