Select the product you need help with

How to troubleshoot a Msg 823 error in SQL Server

Article ID: 2015755 - View products that this article applies to.

Expand all | Collapse all

Symptoms

You may encounter an error message like the following in the SQL Server ERRORLOG or Windows Application Event Log.

2010-03-06 22:41:19.55 spid58 Error: 823, Severity: 24, State: 2.
2010-03-06 22:41:19.55 spid58 The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0x000000a72c0000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\my_db.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. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

2009-12-09 04:53:49.170 spid25s Error: 823, Severity: 24, State: 2. 2009-12-09 04:53:49.170 spid25s The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x00000000132000 in file 'D:\tempdb\tempdb_data.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. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

If an application encounters this message while querying or modifying data, the error message is returned to the application and the database connection is terminated.

Back to the top | Give Feedback

Cause

SQL Server uses Windows API's [e.g. ReadFile, WriteFile, ReadFileScatter, WriteFileGather] to perform the I/O operations. After performing these I/O operations, SQL Server checks for any error conditions associated with these API calls. If these API calls fail with an Operating System error, then SQL Server reports Error 823.

The 823 error message contains the following information:

The 823 error message usually indicates that there is a problem with underlying storage system or the hardware or a driver that is in the path of the I/O request. You can encounter this error when there are inconsistencies in the file system or if the database file is damaged.

Back to the top | Give Feedback

Resolution

Review the suspect_pages table in msdb to check if other pages [in the same database or different databases] are encountering this problem.
Check the consistency of the databases that are located in the same volume [as the one reported in the 823 message] using DBCC CHECKDB command. If you find inconsistencies from the DBCC CHECKDB command, please use the guidance from Knowledge Base article How to troubleshoot database consistency errors reported by DBCC CHECKB.
Review the Windows Event logs for any errors or messages reported from the Operating System or a Storage Device or a Device Driver. If they are related to this error in some manner, please address those errors first. For example, apart from the 823 message, you may also notice an event like "The driver detected a controller error on \Device\Harddisk4\DR4" reported by the Disk source in the Event Log. In that case, you have to evaluate if this file is present on this device and then first correct those disk errors.
Use the SQLIOSim utility to find out if these 823 errors can be reproduced outside of regular SQL Server I/O requests. Note the SQLIOSim tool ships with SQL Server 2008 so there is no need for a separate download on this version and later.
Work with your hardware vendor or device manufacturer to ensure

The hardware devices and the configuration confirms to the I/O requirements of SQL Server
The device drivers and other supporting software components of all devices in the I/O path are updated

If the hardware vendor or device manufacturer provided you with any diagnostic utilities, please use them to evaluate the health of the I/O system
Evaluate if there are Filter Drivers that exist in the path of these I/O requests that encounter problems.

Check if there are any updates to these filter drivers
Can these filter drivers be removed or disabled to observe if the problem that results in the 823 error goes away

Check to make sure you are not running into any Known issue that can result in a 823 error to be reported:

Back to the top | Give Feedback

If the I/O operation involved reading from a database page, SQL Server has in-built mechanism to retry the read I/O operation and if all the retry operations fail, then SQL Server reports the Error 823. If the retry operation succeeds, the query will not fail but a message will be written into the ERRORLOG and Event Log. Refer to these sources for more information:

How to troubleshoot Msg 825 (read-retry) in SQL Server

SQL Server Books Online topic : SQL Server Error 825

For more information about I/O errors in general including 823, see Microsoft SQL Server I/O Basics, Chapter 2.

Back to the top | Give Feedback

Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use

(http://go.microsoft.com/fwlink/?LinkId=151500)

for other considerations.

Back to the top | Give Feedback