Menu Hide

How to recover SQL Server Data with INCORRECT CHECKSUM ERROR?

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.

Stellar Data Recovery offers a complete range of data recovery option to recover deleted data according to different data loss scenarios. Choose the most preferable data recovery option as per your need.

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:

  • Querying to read a page
  • Running DBCC CHKDB command
  • Performing backup operation

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:

  • Expected and actual Checksum values
  • The affected database file
  • The affected page number
  • The type of operation for which the error occurred—Read or Write

The causes of data inconsistency reported by Incorrect Checksum error might be the followings:

  • Corrupt data
  • Corrupt Database
  • Corrupt file system
  • Faulty device driver
  • Problem with underlying storage system
  • Failing storage device
  • Disk firmware issues
  • Other hardware issues

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.

SQL Repair Services - Stellar

Fix Incorrect Checksum Error and Recover 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:

  • Review the Windows Event logs
    Navigate to Windows Event Viewer to look for any errors or messages reported by the Operating System or a Storage Device or a Device Driver. If they are related to Checksum error, try to fix those errors first. For instance, you see the event "The driver detected a controller error on \Device\Harddisk4\DR4" reported by the Disk source in the Event Log. So, first correct those disk errors. You never know if it was this issue with disk that was responsible for Incorrect Checksum error.
  • Run CHKDSK command
    The CHKDSK command checks and fixes the disk-related issues, such as file system related issues or bad sectors, etc.
  • Check if I/O requirements are met
    Check if the hardware devices and the configuration confirms to the I/O requirements of SQL Server
  • Update the device drivers
    Check if the device drivers of all devices in the I/O path are updated
  • Disable the filter drivers
    Check if there’s a scope to disable the filter drivers. If yes, disable them and see the results.
  • Test the integrity of I/O
    Consider testing the integrity of I/O for the disk system. You may consider to use a utility like SQLIOSim or SQLIOStress
  • Check for any other errors reported by SQL Server such as Access Violations or Assertions
  • Disable write-caching
    Check to confirm that the SQL server doesn’t have write-caching enabled on the disk controller.
  • Run Hardware Diagnostics
    Typically the hardware manufacturers provide utilities that diagnose hardware issues. Run those utilities for the computer and/or disk system.

    Check if any hard failure is reported by the Windows system and application logs, or by the SQL Server error log. If you find any, try to swap different hardware components to isolate the problem and fix any hardware issues. If multiple hardware components seem to be the culprits, switch to a new hardware system.

    [CAUTION]: If the cause of the error is a malfunctioning hardware component, you must avoid hit and trial methods. In such a case, you must consider contacting a Professional Data Recovery Service Provider to prevent permanent data loss.

If the above given methods couldn’t fix the SQL Checksum error, refer the below given methods:

  • Restore from last known good backup
    If you have a ‘clean’ backup of the database and transaction logs, restore them from the backup.
  • Run the DBCC CHECKDB command and Repair file(s)
    DBCC CHECKDB command checks the physical and logical consistency of database. These checks include consistency check of database pages, rows, allocation pages, index relationships, system table referential integrity. Additionally, this command also performs other structure checks.

    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:

  1. Run the following set of commands to set the affected database to single-user mode.

    ALTER DATABASE Affected_DB
    SET SINGLE_USER;
    GO

  2. Try to repair the errors that were found in the affected table(s):

    USE Affected_DB;
    GO
    DBCC CHECKTABLE('Corrupt_Table', REPAIR_ALLOW_DATA_LOSS);
    GO

    [CAUTION]: REPAIR_ALLOW_DATA_LOSS may result in permanent data loss.

  3. Change the database back to Multi-user mode. Run the following commands to do this:

    ALTER DATABASE Affected_DB
    SET MULTI_USER;
    GO

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 to avail SQL database repair service 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.

Also Read: How to Repair Corrupted SQL Server Database?

Special Offer from Stellar

Get exclusive offer on stellar data recovery software, drop email to help@stellarinfo.com

100% of people found this article helpful


Most Popular

Recent Knowledge Base