Checksum is a significant indicator of the integrity of the data being read. The stored data is associated with hexadecimal value called Checksum. If this doesn’t match the Checksum as calculated after the read operation, your SQL server throws Checksum error. This potentially signifies that there is some issue with the subjected data or I/O operation subsystem.
The SQL server performs I/O operation to read the disk and transfer the data when you carry out any of the activities such as:
During the I/O operation, the SQL server also performs the logical consistency checks like Checksum to warn about any inconsistency involved in data transfer. If it finds Checksum failure, it throws an error message. And the complete error message looks like the one given below:
Msg 824, Level 24, State 2, Line 1 SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x576c398b; actual: 0x536c398b). It occurred during a read of page (1:43517) in database ID 8 at offset 0x000000025ee000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\xxxx.mdf: MSSQL_DBCC8'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online".
This incorrect checksum error tells that the page was read successfully from the disk by the I/O operation, but the transferred data might have encountered inconsistency.
The error message contains the additional details which are as follows:
The causes of data inconsistency reported by Incorrect Checksum error might be the followings:
As discussed, the underlying cause of SQL checksum error could be any of the ones mentioned above, there’s a lot of diagnostics that you would need to perform. Although there are tools that you can use to recover the data easily after you’ve encountered Incorrect Checksum error, you can follow the article to try to fix it on your own.
Given below is what needs to be checked and what action is needed to fix this SQL error and recover the SQL Server data.
As the cause of this error is NOT always the corrupt database or data, restore or repair should be the last options to fix the Checksum error and recover the data. Instead, you must try to diagnose the possibility of other basic causes, and fix them first. Let’s start troubleshooting the Incorrect Checksum error:
If the above given methods couldn’t fix the SQL Checksum error, refer the below given methods:
You can run this command for the affected database to check its consistency, since you already have the details of the affected Database from the Checksum error.
Run the DBCC CHECKDB command on the affected sever for the affected Database:
DBCC CHECKDB (Affected_DB) WITH NO_INFOMSGS, ALL_ERRORMSGS
If it doesn’t show the results about the consistency, and instead throws an error stating similar to stating that the command couldn’t be run, include TABLOCK as shown below:
DBCC CHECKDB (Affected_DB) WITH NO_INFOMSGS, ALL_ERRORMSGS, TABLOCK
Note: TABLOCK limits the checks that are performed; DBCC CHECKCATALOG is not run on the database, and Service Broker data is not validated.
The results returned by DBCC CHECKDB command looks something like the following:
CHECKDB found 0 allocation errors and 10 consistency errors in database Affected_DB'. Repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Affected_DB)
Running this command, you’ll get to know the details of the issue(s) with the database. For instance, it’ll tell you which is the affected table(s), and what’s the minimum repair level required to repair.
Note: One of the two minimum repair levels—REPAIR_ALLOW_DATA_LOSS | REPAIR_REBUILD—are recommended in the results of the CHECKDB command.
[Caution]: Using REPAIR_ALLOW_DATA_LOSS level while trying to repair may lead to permanent data loss.
Once you know the affected table and the minimum repair level, you can try to repair the affected table. To do this, follow the below given steps:
ALTER DATABASE Affected_DB
DBCC CHECKTABLE('Corrupt_Table', REPAIR_ALLOW_DATA_LOSS);
[CAUTION]: REPAIR_ALLOW_DATA_LOSS may result in permanent data loss.
ALTER DATABASE Affected_DB
The methods outlined in this article should help you resolve the issue and recover the data, but if in case the issue remains unresolved, you must contact a Professional Data Recovery Service Provider like Stellar instead of using hit and trial methods. Alternatively, you can use repair tools like Stellar Repair for MS SQL to recover the SQL server data affected with Incorrect Checksum error.