2009-11-02 15:46:42.90 spid51SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:43686; actual 0:0). It occurred during a read of page (1:43686) in database ID 23 at offset 0x0000001554c000 in file 'H:\MSSQL.SQL2008\MSSQL\DATA\my_db.mdf'.Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe 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 the Error 823. There can be situations where the Windows API call actually succeeds but the data transferred by the I/O operation might have encountered a logical consistency problem. These logical consistency problems are reported through Error 824.
The 824 error contains the following information:
The database file against which the I/O operation is performed
The offset with the file where the I/O operation was attempted
The database to which this file belongs
The page number which was involved in the I/O operation
Was the operation a read or write operation
Details about the logical consistency check that failed [The type of check, actual value and expected value used for this check]
These logical consistency checks are additional integrity checks performed by SQL Server to ensure certain key aspects of the data that was involved in the data transfer was maintained through out the I/O Operation. The checks include checksum, Torn Page, Short Transfer, Bad Page Id, Stale Read, Page Audit Failure. The nature of the checks performed vary depending on different configuration options at the database and server level.
The 824 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.
If the database that encounters these 824 errors does not have the PAGE_VERIFY CHECKSUM database option turned on, please do so immediately. 824 errors can occur for other reasons than a checksum failure but CHECKSUM provides the best option to verify consistency of the page after it has been written to disk.
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 824 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 824 errors can be reproduced outside of regular SQL Server I/O requests. Note that SQLIOSim ships with SQL Server 2008 so there is no need for a separate download on this version or later.
Work with your hardware vendor or device manufacturer to ensure:
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 824 error goes away
If the I/O operation failed 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 and a message is written into the SQL Server ERRORLOG and Windows Application Event Log. For more information see these resources:
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 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, Microsoft SQL Server 2012 Developer, Microsoft SQL Server 2012 Enterprise, Microsoft SQL Server 2012 Express, Microsoft SQL Server 2012 Standard, Microsoft SQL Server 2012 Web, Microsoft SQL Server 2014 Developer, Microsoft SQL Server 2014 Enterprise, Microsoft SQL Server 2014 Express, Microsoft SQL Server 2014 Standard, Microsoft SQL Server 2014 Web