2

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.

First Update

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.

Second Update

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.

Thanks, Kevin

  • Out of curiosity, what is the SAN solution are you using? I've seen these errors most often on SANs that are configured with one large disk pool/storage pool/volume group as opposed to segregated containers that separate out the workloads. You may want to ask your storage admins about the configuration as well in case they have the ability to carve out some storage formally dedicated to SQL Server workloads. – John Eisbrener May 10 at 15:39
  • We are using a dell compellent san. The SAN is dedicated for the sql server, no other applications are using it. Interesting point about the configuration, I'll need to discuss with our storage team as I don't have those details. – kevinnwhat May 10 at 15:43
  • I wonder if you're running up against a noisy neighbor scenario, especially if you're got shared pools/groups in use. – John Eisbrener May 10 at 15:54
  • Noisy Neighbor... fitting name! I'll share with our infrastructure team. – kevinnwhat May 10 at 16:06
5

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?

There really isn't anything you can do from the database side. SQL Server is a victim of the underlying hardware and virtualization (if any) having issues. The underlying issue (driver, hardware, config, etc.) needs to be fixed. Note that if you're in a virtualized environment it could be a software layer in between or an issue with the host/guest config, etc., and not a physical hardware or storage issue.

Realistically, removing all the filter drivers and associated software, stopping and in-between layers by removing them and putting this on physical (if virtual) and/or changing storage solutions (for example, using local instead of remote/SAN) can help aid in troubleshooting the issue. Updating drivers (for example multipathing, device, firmware, etc.) could also be helpful but not something I would charge a DBA to do but a datacenter or systems admin.

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?

Not really. Underneath we're calling read and write API's through Windows. The return code from the API call through Windows is this Windows error code which we're bubbling up so that the admin of SQL Server knows why SQL Server is having issues.

If anything, since it's a single volume they should be able to isolate that on the backend and enable infrastructure tracing. If this is a physical machine this would be from the HBA/scsi controller and below through the hardware. If it's virtual, then from the Host through the same layers.

Sadly, this is easier to say than do and most places are ill equipped to actually investigate these style of issue - especially when the environment is virutalized.

Final Thoughts

What do the system event logs say? Are there further NTFS or other corruption issues coming up? Are devices being reset? The system event log should be dissected with an extremely fine toothed comb to see if there are a series of events or items that seem to lead up to this or if it's spontaneous. Additionally I've found these events to generally cluster around certain items such as times of high use on a specific controller or overhwelmed SANs.

  • Hi Sean, thanks for your feedback. When I reviewed the system event log I found mostly the same errors as in the sql server log, ie 'log for <Db> is not available' but I am going to do another review of that and relay some of this information to our storage team. – kevinnwhat May 1 at 17:12
  • @kevinnwhat That's great you've already looked at it! This is the best record we have of what's happening at the system level. Feel free to update the question with any new information you find and we can take a look :) I'd see if maybe they can give you storage on a different SAN and see if that changes teh behavior? – Sean Gallardy May 1 at 17:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.