Summary: Database inconsistency errors can occur in SQL Server nodes connected in cluster failover due to different reasons. In this post, we will discuss the possible reasons behind the DB inconsistency errors in SQL Server Cluster Failover. We will also mention an advanced SQL Server database repair software that can help recover data from corrupted SQL database files with integrity.
Failover cluster is one of the database availability features in MS SQL Server. The failover cluster setup contains a group of independent servers (nodes) sharing a common storage system. If any of the nodes fail, the SQL Server services are automatically transferred from the failed node to another node in the cluster.
However, sometimes, the nodes in the SQL Server cluster fail due to various issues, like incorrect synchronization between nodes or hardware issues on the shared storage system. After cluster failover, you can face logical consistency-based I/O errors and issues. One such error 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 this article, we’ll discuss the methods to resolve the DB inconsistency errors in SQL Server Cluster Failover.
Causes of SQL Server Cluster Failover DB Inconsistency Error
Here are some possible reasons that can lead to this error in SQL Server:
- Problems with the NIC Card that is preventing correct synchronization between the nodes.
- Problems or issues with the hard drive.
- Damaged or failing hardware.
- Viruses or malware infection.
How to Check Inconsistency Issues in SQL Database?
You can run the DBCC CHECKDB command to check inconsistency issues in SQL database file (see the below example).
DBCC CHECKDB ‘database_name’;This command will check inconsistencies in the tables, data pages, and indexes of the SQL database. If it detects integrity or inconsistencies issues in the database, it will display consistency errors, along with description and also recommend the repair option to fix the issues.
When checking the database with DBCC CHECKDB after a cluster move from one server to another, you may encounter checksum error indicating the integrity issues and consistencies in data pages. In this case, you can use the DBCC PAGE command as given below to check the status of the page.
DBCC TRACEON (3604); DBCC PAGE (database, 1, 132, 3)
DBCC TRACEOFF (3604);Alternatively, you can use the PAGE_VERIFY option to detect page integrity issue in the database (see the below example).
SELECT name, page_verify_option_desc FROM sys.databasesYou can also check the SQL error log for errors related to inconsistencies in a failover cluster.
Solutions to Fix the DB Inconsistency Error in SQL Server Cluster Failover
If there are inconsistency issues in the MS SQL database in connected nodes in the SQL Server cluster failover, then follow the below methods to resolve them.
1. Check the Cluster Health
If you’re encountering this issue in a failover cluster, then first check the cluster’s 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 Network Interface Card (NIC) is used to synchronize information between nodes. The NIC is related to performance of the failover. It is used 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 Database in SQL Server Cluster Failover?
The inconsistency issues can corrupt the SQL database. If your database in failover cluster is corrupted, you can recover the database from backup. You can use the SQL Server Management Studio (SSMS) to recover your database from backup. Here are the steps:
- Open the SQL Server Management Studio (SSMS).
- Select the databases folder, right-click on it, and select the Restore Database option.
- The Restore Database window is displayed. On the General page, under the Source section, select any of these options:
- Choose the Database option and then select the database you want to restore from the dropdown list.
- Select the Device option and click the ellipses (…) button to go to your backup file location.
- In the Select backup devices window, choose File as backup media and then press the Add button.
- Select the backup file and press OK.
- When the “Restore of database completed successfully” message pops up, click OK.
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
GOThe above command will restore the database – named stellardb, from the stellar.bak file.
What to do if the backup set is corrupt?
In case the database backup is corrupted or you do not have an updated backup, then you can repair the SQL database using the DBCC CHECKDB command. Using REPAIR_ALLOW_DATA_LOSS option with the DBCC CHECKDB command can resolve inconsistency errors in SQL database but it can deallocate data, resulting in data loss.
To avoid data loss, you can use Stellar Repair for MS SQL Technician – an advanced SQL repair software. This software can quickly repair the corrupted SQL Server database (MDF/NDF) file and save all the data to a new SQL database file without any data loss. You can save the recovered data in a new database, live database, or different formats like Excel, CSV, and HTML.
The software can also recover deleted records from the corrupted/damaged SQL database file and .BAK file. It can resolve several errors related to data corruption in SQL database, including 829, 823, 824, 2508, 8993, 2511, and 7995.
Conclusion
You can encounter DB inconsistency errors in SQL Server cluster failover due to different reasons. Above, we have mentioned the solutions to resolve the inconsistency errors in SQL Server Cluster Failover. If the database is corrupted, you can 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 with complete integrity. It has no file-size limitations. You can download the demo version of the Stellar Repair for MS SQL Technician software to evaluate its functionality.
[NS1]Mark this nofollow
