How to troubleshoot a Msg 823 error in SQL Server

Article translations Article translations
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.

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 database file against which the I/O operation was performed
  • The offset within the file where the I/O operation was attempted
  • Is the I/O operation a read or write request
  • The Operating System Error code and Error Description
 
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.

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:

More Information

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
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 for other considerations.

Properties

Article ID: 2015755 - Last Review: April 1, 2010 - Revision: 1.0
APPLIES TO
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Express
  • Microsoft SQL Server 2008 Express with Advanced Services
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 R2 Web
  • Microsoft SQL Server 2008 R2 Workgroup
Keywords: 
KB2015755

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com