- 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.
- 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.
- 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:
- FIX: The Database Mirroring session is disconnected when you manually perform a failover in SQL Server 2008
- FIX: Error message when you run the CHECKPOINT statement after you restore a database that has a read-only user-defined filegroup in SQL Server 2005: "The operating system returned error 5(Access is denied.) to SQL Server"
How to troubleshoot Msg 825 (read-retry) in SQL Server