Dear Friends,
There are different scenarios where read operation fails in first attempt and when retried gets successful may be in second, third or even fourth attempt. SQL Server development team adopted this strategy and any read failure 823 or 824 will be tried four times before SQL Engine reports a failure.
As per MDSN, SQL Server uses Windows API’s 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. 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.
If in any of the read attempts succeeds while retying, you will observe similar message on SQL Server ERROR 825 LOG as pasted below;
A read of the file ‘D:\Data\MyDatabase\MyDatabase.mdf’ at offset 0x0000005fd51000 succeeded after failing 2 time(s) with error: incorrect pageid (expected 3:241); actual 0:0).
Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Users will not see any error messages but there could be an impact on performance causing slight delay in application behaviour. This does not mean that we do not need to take corrective actions as such type of retry operations may fail in future attempts. This should be treated the same way as we do in case of error 823 or 824 to avoid any disaster.
Let me know if you have experienced similar issues and the way it was resolved.
Regards
Kanchan Bhattacharyya
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
