I/O requests taking longer than 15 seconds to complete on file OS file handle is 0x00000000000005C

Abdul Baba Syed 21 Reputation points
2021-03-04T18:13:58.507+00:00

Hi

Sql server 2008 R2 memory is full and we are seeing this below error
continuously in the log file, even after every restart sql server goes in to recovery mode after that it will be fine and the memory will be full again in few hours, but database is operational

SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\SQLDATA\fff\cccc.ldf] in database [dd] (5). The OS file handle is 0x00000000000005C8. The offset of the latest long I/O is: 0x00000062bc6000

we are seeing this error for .ldf, .ndf. mdf,etc

fyi

just done dbcc checkdb, could not find any errors on database.
just noticed one table 74435-image.png

and rebuild index, shrink jobs are failing, but backup jobs are ok! even i could not shrink the log files, which is pretty huge..

we cant find any events in the windows 2008 R2 Server

this a virtual server running on vmware

Appreciate your help on this.. Thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,756 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,716 Reputation points
    2021-03-04T21:00:03.217+00:00

    The warning "I/O requests taking longer than 15 seconds" is SQL Server reporting you have a serious problem with your storage hardware and you need to fix it.

    You need to look at your storage hardware for errors.

    It is completely normal and expected for SQL Server to use all available RAM. Please see: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-ver15

    0 comments
  2. Sean Gallardy - MSFT 1,886 Reputation points Microsoft Employee
    2021-03-04T21:03:35.183+00:00

    Sql server 2008 R2 memory is full and we are seeing this below error
    continuously in the log file, even after every restart sql server goes in to recovery mode after that it will be fine and the memory will be full again in few hours, but >database is operational

    There's a lot to unpack there.

    Full memory: Make sure your max server memory is set correctly. Remember that SQL Server uses memory as a cache, so memory usage isn't a bad thing.

    15 Second+ IO Messages: This is a warning to check your server setup and configuration, along with storage and any fabric in between. This means that SQL Server called the Windows APIs to read or write data, but after checking on the status of that request sees that it has no yet been completed. This is not a SQL Server issue, it's letting you know that you have other potential issues in your environment. If you're constantly seeing this, then the previously mentioned items need to be checked. For example, on a VM, an administrator can set IOP limits which can slow down and artificially limit IO.

    SQL Server 2008R2: This is no longer supported. We all know it but I have to say it.

    SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\SQLDATA\fff\cccc.ldf] in database [dd] (5). The OS file handle is 0x00000000000005C8. The offset of the latest long I/O is: 0x00000062bc6000

    This is letting you know that at least 1 IO is taking longer than 15 seconds. It gives you the location of the file on disk and the offset of the operation inside of the file for different levels of possible troubleshooting. Please see what I wrote above. If SQL Server is pushing too much IO where the storage can't keep up with it (or there are other issues, like dropping packets on iscsi networks, etc.) then you can look at your queries and see if there are very large write operations that are causing the churn and see if you can tune those.

    Doing things that cause more IO such as rebuilding indexes is only going to make things worse.

    0 comments
  3. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2021-03-05T08:26:38.29+00:00

    Hi @Abdul Baba Syed ,

    > SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\SQLDATA\fff\cccc.ldf] in database [dd] (5). The OS file handle is 0x00000000000005C8. The offset of the latest long I/O is: 0x00000062bc6000

    This indicates SQL Server I/O Bottlenecks. SQL Server performance highly relies on the Disk performance. Suggest you using some dmvs from below document to check the reasons for I/O Bottleneck, then try the suggestions from below document to resolve the issue.

    I/O requests taking longer than 15 seconds to complete on file

    If the response is helpful, please click "Accept Answer" and upvote it, thank you.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments