question

AbdulBabaSyed-3708 avatar image
0 Votes"
AbdulBabaSyed-3708 asked ·

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

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-general
image.png (2.0 KiB)
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

We have not received a response from you. Did the replies could help you? If the response helped, do "Accept Answer". If it is not, please let us know.

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered ·

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://docs.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-ver15

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SeanGallardy-MSFT avatar image
0 Votes"
SeanGallardy-MSFT answered ·

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered ·

Hi @AbdulBabaSyed-3708,

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.


·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.