How to Resolve DB Inconsistency Error in SQL Server Cluster Failover?

It is not uncommon for users to encounter DB inconsistency errors in SQL Server. One typical error that some users have reported after doing a cluster move from one server to another and then checking the database with DBCC CHECKDB is:

SQL Server detected a logical consistency-based I/O error:incorrect checksum. It occurs during read of page(11:132) in database ID 3 at offset 0x0000000013c))) in file ‘F:\ MSSQL\DATA\MSDB.mdf.

In the above error, the page (11:132) means that the page number is 132 and the file number is 11.

You can run the following query to get the file IDs of the database:

SELECT file_id, name
FROM sys.master_files
WHERE name like 'stellardb%database name'

Once you have the page ID, you can use the DBCC page to check the status of the page.

DBCC TRACEON (3604); 
DBCC PAGE (database, 1, 132, 3)
DBCC TRACEOFF (3604);

Here, 1 represents the file ID, 132 is the page number, and 3 is to display the header and detailed row information.

You can also check the error log for errors related to inconsistency in a failover cluster. The following extended stored procedure can help you to look for errors:

EXEC sp_readerrorlog 0,1,N'stellardb','incorrect checksum';

This stored procedure reads the SQL Server log. Here, 0 is to read the current error log and 1 is to check for SQL Server errors. Stellardb is the name of the database and incorrect checksum is the word to look for in the error log.

Causes of SQL Server Cluster Failover DB Inconsistency Error

Here are some possible reasons for this error in SQL Server:

• Problems with the NIC Card that is preventing correct synchronization between the nodes and causing problems in the consistency of data.
• Problems or issues with the hard drive.
• Damaged or failing hardware.
• Viruses or malware infection.

Solutions to Fix the DB Inconsistency Error in SQL Server Cluster Failover

You can try the following solutions to fix the DB inconsistency error in SQL Server.

1. Check the Cluster Health

If you’re encountering this error in a failover cluster, then first check the cluster health. To do that, follow the below steps:

• Run the Failover Cluster Management.
• Go to Management and click to validate the configuration.
• Then, follow the wizard to run the text and check the summary to verify errors.

2. Install the Latest Updates

Sometimes, outdated Windows Server version leads to issues and errors. So, make sure the latest hotfixes and updates are installed on your Windows Server. Microsoft also releases updates related to the failover cluster. You can check the update history for the latest Windows Server 2022 on Microsoft support page. For older versions, you can check the Microsoft® Update Catalog.

3. Check the NIC card

The NIC (Network Interface Card) is used to synchronize information between nodes. The NIC is related to the performance of the failover. You use the NIC to communicate between the cluster nodes. The cluster uses heartbeat signals to check the status of the nodes and does a failover in case a node fails. If the NIC has problems, the synchronization between nodes will fail and cause corruption in the database. You can check and change the NIC to fix the issue.

How to Recover the Database?

If your database in failover cluster is corrupted, you can recover the database from the backup. You can use the SQL Server Management Studio to recover your database from backup. Here are the steps:

• Download the SQL Server Management Studio (SSMS).
• Once installed, open the SSMS.
• Select the databases folder, right-click on it, and select the Restore Database option.

• Select the Device option and press the Browse button.

• In the Select backup devices window, press the Add button.

• Select the backup sets and press OK to restore the backup.

Alternatively, you can use the following T-SQL command to restore the database from backup:

USE [master]
RESTORE DATABASE [stellardb] FROM  DISK = N'C:\MSSQL\Backup\stellar.bak' WITH  FILE = 1,  MOVE N'AdventureWorks2017' TO N'C:\data\stellardb.mdf',  MOVE N'AdventureWorks2017_log' TO N'C:\data\stellardb_log.ldf',  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE DATABASE [stellardb] FROM  DISK = N'C:\MSSQL\Backup\stellar.bak' WITH  FILE = 2,  NOUNLOAD,  STATS = 5

GO

The above command will restore the database, named stellardb, from the stellar.bak backup.

What to do if the backup set is corrupt?

In case the database backup is corrupted or you do not have a current backup, then you can take the help of an advanced SQL repair software, called Stellar Repair for MS SQL – Technician. This software can repair the corrupted SQL Server databases and can also recover data from corrupted SQL Server backup (.bak) files.

There is a Repair MS SQL Database module in the software that is used for repairing the SQL Server database files and the Extract from MS SQL Backup module can recover data from the backup (.bak) file. It can save the recovered data in a new database file, in a current database file (offline), and to an Excel, CSV, or HTML file.

Conclusion

You can encounter DB inconsistency errors in SQL Server due to different reasons. Above, we have mentioned the solutions to resolve the inconsistency errors in SQL Server Cluster Failover. Once the problem is resolved, you need to recover the database from the last known good backup. If you do not have a current backup or the backup is corrupted, you can use Stellar Repair for MS SQL – Technician. This software can recover data even from corrupted backup files. You can download the demo version of the Stellar Repair for MS SQL – Technician software to evaluate its functionality.