How to fix SQL Error 823

SQL Issues and Solutions

Are you facing SQL error 823? This article will help you fix it.

SQL Server error 823 is usually triggered when there is an issue with the IO or operating system in the SQL server.

As part of our Server Support Services here at Ibmi Media, we have dealt with numerous complaints from our customers regarding SQL related errors.

This context will help you know more about this error and how to fix it.

What triggers SQL Error 823?

In an SQL Server, I/O operations are handled by ReadFile, WriteFile, ReadFileScatter, WriteFileGather Windows APIs.
With the help of Application Programming Interface or API in the SQL Server, these I/O operations functions. After the I/O operation processes is completed, errors relating with the API calls will be checked.

Sometimes, these API calls fails and triggers an SQL Error 823 due to issues with the IO or the operating system.

Additionally, the API server might be incompatible due to System corruption, Hardware issues, Database corruption and System errors.

How to solve SQL Error 823?

When this error results from issues with the Operating system, you will see an error message such as;

Error: 823, Severity: 24, State: 2.

2020-03-06 22:41:19.55 spid58 The operating system returned error 1117 (The request could not be performed because of an I/O device error.) to SQL Server during a read at offset 0x0000002d460000 in file 'e:\program files\Microsoft SQL Server\mssql\data\mydb.MDF'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe, system-level error condition that threatens database integrity and must be corrected immediately. It is recommended to complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

To fix this error, it is good to have a look at the suspect_pages table in the MSDB.
Moreover, inconsistency of the database's location on the same volume should be checked with the CHECKDB command. If found that the database is inconsistent, the best way to fix it is to restore it from a recent working backup.

In some other cases, SQL Error 823 can be as a result of an IO error. Issues with the Torn Page, Bad page ID and transferring insufficient bytes can be the reason why this error would occur.
To fix this, first run the DBCC CHECKDB command to repair the database as seen below;