The error of “SQL Server detected a logical consistency-based I/O error: incorrect checksum” might occur when you Inserting, reading, updating, backup or generating reports.

“SQL Server detected a logical consistency-based I/O error: incorrect checksum” will occur mostly due to system failed to identify and recognize the data in table

It is always recommended to restore with your latest healthy backup, if you don’t have it then can refer to this guide

Commonly address as database corruption and there are multiple type of corruption as below:

• SQL page-level corruption: page-level corruption occurs when the information or data stored in the header, body, or slot array of a database page is altered such that the user cannot access the page. Page-level corruption can happen due to reasons like hardware issues, disk/sub-system failure, malware, faulty updates and patches, etc.
• Boot page corruption: this is a more critical case of SQL database corruption as it concerns the boot page. There is only one boot page per SQL database, and it stores the metadata for the entire database. So, its corruption can affect the whole database file. Further, DBCC CHECKDB or page-level restore cannot fix the boot page corruption. This limitation is given the fact that the boot page stores Meta information like the current version, database ID, checkpoint LSN, etc.
• Non-clustered index corruption: this type of corruption is associated with SQL Server 2008 and later versions. It typically occurs when a SQL DBA attempts running a complex UPDATE statement with NOLOCK hint against a table. Non-clustered index corruption leads to incorrect reading of the SQL database query or multiple read operations on the same value.
• SQL database in suspect mode: SQL database suspect mode is a frequent issue DBAs face due to damage in the primary file group file, which stops the database recovery during SQL Server startup. A SQL Server may mark the database in SUSPECT mode after detecting a problem in the log file due to reasons like hardware malfunction, disk space issue, system crash, etc. A database in suspect mode fails to perform the read/write operations leading to downtime.

Verify the corruption of database

1. Connect your database with SSMS (SQL Server Management Studio)

https://support.qne.com.my/a/solutions/articles/81000385373 

2. Right-click your server instance in Object Explorer, and then select New Query

3. Paste the following T-SQL code snippet into the query window:
4. To find out objects that caused “SQL Server detected a logical consistency-based I/O error: incorrect checksum”

-----Replace [SampleDB_Testing] to your database name-----

USE Master

GO

DBCC CHECKDB (SampleDB_Testing) WITH ALL_ERRORMSGS, NO_INFOMSGS

5. Execute the query by selecting Execute or selecting F5 on your keyboard.

6. The results of the query are displayed under the area where the text was entered.
7. If there aren’t any corruption, it will show “Commands completed successfully”
8. Else it will show Logical Consistency-Based I/O Error in which table
9. For our sample, it shows there are 8 unrecognize data in table SalesInvoiceDetails that stored all the detail like Stock Code, Qty, Unit Price and etc. in Sales Invoice transaction that caused “SQL Server detected a logical consistency-based I/O error: incorrect checksum”

Repair / Recover the unrecognize / corrupted data

1. Right-click your server instance in Object Explorer, and then select New Query

2. First, we will try to repair “SQL Server detected a logical consistency-based I/O error: incorrect checksum” without data loss
3. Paste the following T-SQL code snippet into the query window:

-----Replace [SampleDB_Testing] to your database name-----

USE MASTER

GO

ALTER DATABASE [SampleDB_Testing] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

USE MASTER

GO

DBCC CHECKDB (SampleDB_Testing, REPAIR_REBUILD) WITH ALL_ERRORMSGS

4. Execute the query by selecting Execute or selecting F5 on your keyboard.

5. The results of the query are displayed under the area where the text was entered.
6. If Repair without data loss success, Bottom of the results will show “DBCC execution completed”
7. Else will show “REPAIR_ALLOW_DATA_LOSS is the minimum repair level for the errors found”
8. If such message show, then we have no choice but try to repair “SQL Server detected a logical consistency-based I/O error: incorrect checksum” with data loss
9. Paste the following T-SQL code snippet into the query window:

-----Replace [SampleDB_Testing] to your database name-----

USE MASTER

GO

ALTER DATABASE [SampleDB_Testing] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

USE MASTER

GO

DBCC CHECKDB (SampleDB_Testing, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS

10. Execute the query by selecting Execute or selecting F5 on your keyboard.

11. You can always execute check for corruption query to double check your database after repair

-----Replace [SampleDB_Testing] to your database name-----

USE Master

GO

DBCC CHECKDB (SampleDB_Testing) WITH ALL_ERRORMSGS, NO_INFOMSGS

12. If you repaired your corrupted database with allow data loss query for “SQL Server detected a logical consistency-based I/O error: incorrect checksum” then it is recommended to compare your current reports with your previous generated reports to identify and re-entry back the unrecoverable data 

Find out more about Set-up Schedule Backup in QNE Optimum

Find out more about Effective SQL Server Monitoring