SQL Error Messages – I/O Related

You may see the following error message in SQL Server 2005 Error logs or event logs:

SQL Server has encountered %d occurrence(s) of I/O requests taking longer than %d seconds to complete on file [%ls] in database [%ls] (%d). The OS file handle is 0x%p. The offset of the latest long I/O is: %#016I64x.

Now, the most important thing to understand over here is that this message is reported by SQL Server but is not necessarily an issue with SQL Server.

SQL Server relies on the underlying Windows APIs to manage read/write operations to disk. It simply issues commands to do the read/write and waits for a completion response. If that response is not received in a timely fashion, this error message will start to appear in the Error logs and Event logs.

Identifying the Cause

SQL Server related error messages and their resolution can be searched by visiting the Events and Error Message Center.

This particular error message is documented at the above link. To identify if your disk subsystem is a bottleneck in this case, follow the recommendations mentioned here.

Resolution

Once you have identified the disk subsystem to be a bottleneck based on the recommendations at the above link, the next step is to consider the possible solutions.

Microsoft has published a great whitepaper that talks about how to troubleshoot disk related issues. Refer to the section which talks about I/O Bottlenecks here.