Error message 823 may indicate hardware problems or system problems in SQL Server (828339)

SYMPTOMS

If you receive the following error message, it might indicate that Microsoft SQL Server 2000 has detected hardware or system problems when it was reading from or writing to database files:

Error 823
I/O error <error> detected during <operation> at offset <offset> in file '<file>'

Note The format of the error message is slightly different in Microsoft SQL Server 2005 and Microsoft SQL Server 7.0. However, the same concepts and discussions also apply to SQL Server 2005 and SQL Server 7.0.

For more information about the various parts of this message, such as <error> and <operation>, and for more information about the use of Microsoft Windows API calls that are made by SQL Server 2000, see the "More Information" section of this article.

RESOLUTION

SQL Server 2000 raises the error that is mentioned in the "Symptoms" section of this article if the following conditions are true:

Operating system error: A read Windows API call or a write Windows API call is not successful, and SQL Server encounters an operating system error that is related to the Windows API call. The following error message is an example of an 823 error for an operating system:
2003-07-28 09:01:27.38 spid75 Error: 823, Severity: 24,
State: 2
2003-07-28 09:01:27.38 spid75 I/O error 1117 (The request could not be performed because of an I/O device error.) detected during read at offset 0x0000002d460000 in file 'e:\program files\Microsoft SQL Server\mssql\data\mydb.MDF'
Except for the operating system error 6 ("The handle is invalid"), operating system errors that are reported for 823 errors are likely related to an underlying system problem or hardware problem. If an operating system error occurs, even if the DBCC CHECKDB statement does not report a problem, you may have to work with your hardware vendor, system administrator, or Microsoft Product Support Services to resolve this problem.

Note You may or may not see errors from the DBCC CHECKDB statement on the database that is associated with the file in the error message. You can run the DBCC CHECKDB statement when you see an 823 error. If the DBCC CHECKDB statement does not report any errors, you probably have an intermittent system problem or a disk problem.
I/O logical check failure: If a read Windows API call or a write Windows API call for a database file is successful, but specific logical checks on the data are not successful (a torn page, for example), an 823 error is raised. The following error message is an example of an 823 error for an I/O logical check failure:
2003-09-05 16:51:18.90 spid17 Error: 823, Severity: 24,
State: 2
2003-09-05 16:51:18.90 spid17 I/O error (torn page) detected during read at offset 0x00000094004000 in file 'F:\SQLData\mydb.MDF'..
To resolve this problem, first run the DBCC CHECKDB statement on the database that is associated with the file in the error message. If the DBCC CHECKDB statement reports errors, correct those errors before you troubleshoot this problem. If the problem persists even after the DBCC CHECKDB errors have been corrected, or if the DBCC CHECKDB statement does not report any errors, review the Microsoft Windows NT system event log for any system errors or disk-related errors. You can also contact your hardware vendor to run any appropriate diagnostics.

MORE INFORMATION

Error Message Details

The parts of the following 823 error message are described here in more detail:

Error 823
I/O error <error> detected during <operation> at offset <offset> in file '<file>'

The 823 error message information can be explained in more detail:

<error>: This can be an operating system error or a logical I/O check failure. For an operating system error, the operating system error number follows "I/O error." The text of the operating system error is included in parentheses after "I/O error error number."

For a logical I/O check failure, the failure message is inside parentheses and can be one of the following:
- (torn page): For more information about torn pages, see SQL Server 2000 Books Online.
- (bad page ID): This message means that the pageID on the page header is not the expected page that was read from the disk. For example, if SQL Server 2000 provides a file offset for database file 1 that is for logical page 100, the pageID on the page header for that 8 KB page should be 1:100. If not, the bad page ID is included in the logical I/O check failure message.
- (insufficient bytes transferred): This problem indicates that the Windows API call was successful, but the bytes that were transferred were not what was expected.
<operation>: This is either read or write.
<offset>: This is the physical byte offset from the start of the file. Dividing this number by 8192 will give you the logical page number that is affected by the error.
<file>: This is the file that is associated with the I/O problem, and it includes its complete physical path.

SQL Server I/O and Windows API

SQL Server 2000 uses standard Windows API calls, such as ReadFile, ReadFileScatter, WriteFile, and WriteFileGather, to perform I/O with its database files. When SQL Server 2000 uses the Windows API calls, the file has already been opened successfully, or SQL Server 2000 would not try to read from it or write to it. Therefore, if a Windows API call is not successful and if the error is anything other than the operating system error 6 ("Invalid Handle"), the error is likely being raised in Windows or by a lower-level software component, such as a device driver. Because the operating system error 6 is an invalid handle, the problem may occur if SQL Server is using an invalid handle to make a Windows API call. However, this may still be a system problem.

For example, if you encounter the following error message in the SQL Server Errorlog file, SQL Server encountered operating system error 2 when it uses a Windows API call to write to the tempdb primary database file:

Error: 823, Severity: 24, State: 4
I/O error 2(The system cannot find the file specified.) detected during write at offset 0x00000000284000 in file 'D:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf'