We have an ongoing issue with our production sql server (physical) where we randomly receive this error in the log which puts the database into a recovery state
SQLServerLogMgr::LogWriter: Operating system error 1117(The request could not be performed because of an I/O device error.) encountered.
The issue always occurs on our drive storing the transaction logs. The database normally recovers on its own, but few instances it does not and we need to restart the instance to recover. No errors returned from any database for dbcc checkdb.
Our storage team has been investigating it for weeks with our vendor, but with no luck. The investigation is on-going.
That being said, how should the sql server dba handle this error aside from reporting it to the storage team and checking for database corruption? I am wondering if there is any more information I may be able to gather from the sql server side which may help in their investigation?
Running SQL Server 2012 SP3, storage is a SAN.
Our infrastructure team made the following changes last night
- Updated the firmware on all NICs on the database server
- Updated the firmware on the network switches
- Enabled Jumbo frames for ICSCI
We haven't received the error yet, I'll update again in a week or so.
The changes made in previous update did not resolve the issue. Last night we moved tempdb from the SAN to local drive on the physical server and we disabled iSCSI optimization connection tracking. We haven't received the error yet, and we also see much faster disk read/writes to our data and log drives (still on the san) and of course tempdb being local. Additionally, we were receiving many iSCSI errors in the windows event logs during the time of error and also throughout the day. Since these changes last night those iCSI errors are mostly gone, there are still some coming in but not nearly as much.