SQL Server error 825, Read Retry

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 TwitterFollow me on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

Leave a Reply

Your email address will not be published. Required fields are marked *