Are you seeking for a solution of SQL error 823? We can help you fix it.

Often, the SQL server error 823 occurs when there is a problem in SQL Server. It can either due to fault in the operating system error or an IO error.

Here at Bobcares, we have seen several such SQL errors as part of our Server Management Services for web hosts, Linux/Windows users, and online service providers.

Today, we’ll take a look at the cause for this error and see how to fix it.

Reasons for SQL Error 823?

SQL Server uses Windows APIs (ReadFile, WriteFile, ReadFileScatter, WriteFileGather) to perform file I/O operations.

Further, the SQL Server uses Application Programming Interface or API to perform file I/O operations in Windows OS. Once finishes I/O operations, SQL Server tests any error conditions related to these API calls

However, if an API calls fail with an Operating System error results in SQL Error 823. Mostly, it happens due to operating system error or an IO error.

Further, the 823 error message contains the following information:

1. A database file that was used for the I / O process.

2. The offset within the file where you tried the I / O process.

3. Indicates if the I/O operation is a read or write request.

4. The Operating System error code and error description.

Also, other reasons behind the API-Server incompatibility are;

1. User errors in the system.

2. System corruption.

3. Database corruption.

4. SQL Server update or reinstallation.

5. Hardware corruption.

How to fix SQL Error 823

Here, let’s see the different solutions provided by our Support Engineers to fix this error.

Operating system error

SQL Server reports an operating system error if a read or write Windows API call is not successful.

And, the error message looks like,

Error: 823, Severity: 24, State: 2.
2010-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.

In order to fix the error, our Support Engineers check the suspect_pages table in MSDB for other pages that having this problem.

Also, verify the consistency of databases located on the same volume using DBCC CHECKDB command. Otherwise, the consistency errors can be eliminated by restoring data from a good backup.

An IO error

Frequently, this error can be related to the following problems,

1. A Torn Page.
2. Bad Page ID.
3. Insufficient bytes transferred.

So, our Support Engineers initially run a DBCC CHECKDB command. then, we try to repair the database using the below command.

DBCC CHECKDB (DB_NAME, REPAIR_REBUILD);

Again, we run “DBCC CHECKDB” to verify whether the database is repaired.

If repairing ends in failure, we restore the database with a backup. Once the database is restored, run a CHECKDB to verify that the problem is solved.

Sometimes, the problem can persist after the restoration or repairing. Mostly, this happens due to a hardware problem, then the customer may need to repair the hard disk.  Or, they need to contact the hardware vendor or device manufacturer to ensure the following,

1.  Firstly, check whether the hardware devices and the configuration conforms to the I/O requirements of SQL Server.

2. Then, the device drivers and other supporting software components for devices in the I/O path are not outdated.

[Need assistance to SQL error 823? We’ll help you.]

Conclusion

In this article, we learned different reasons that produced the SQL Server error 823. Mostly, this problem can be caused by hardware (disk errors, power failures) or system problems. Today, we saw how our Support Engineers provide a work-around for this error.