Answered by:
error in the log

Question
-
We recently got message in sql server log like below:
What could be wrong:
SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [F:\MSSQL\LOG\MBAM Recovery and Hardware_Primary.ldf] in database id 10. The OS file handle is 0x0000000000001008. The offset of the latest long I/O is: 0x0000009d303600. The duration of the long I/O is: 32629 ms.
Message
FlushCache: cleaned up 8690 bufs with 7095 writes in 211148 ms (avoided 5100 new dirty bufs) for db 12:0Message
average writes per second: 33.60 writes/sec
average throughput: 0.32 MB/sec, I/O saturation: 100, context switches 332SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [F:\MSSQL\LOG\SUSDB_log.ldf] in database id 11. The OS file handle is 0x0000000000000FF4. The offset of the latest long I/O is: 0x00000016ac0800. The duration of the long I/O is: 45448 ms.
Message
last target outstanding: 19200, avgWriteLatency 0Source Server
Message
Long Sync IO: Scheduler 3 had 1 Sync IOs in nonpreemptive mode longer than 1000 msMessage
FlushCache: cleaned up 24141 bufs with 16849 writes in 181911 ms (avoided 25301 new dirty bufs) for db 12:0Message
average writes per second: 92.62 writes/sec
average throughput: 1.03 MB/sec, I/O saturation: 101, context switches 458
Message
last target outstanding: 19200, avgWriteLatency 0Message
A time-out occurred while waiting for buffer latch -- type 3, bp 000001BDA73F7740, page 1:42954663, stat 0x10b, database id: 12, allocation unit Id: 72057622850764800, task 0x000001BD4C033468 : 0, waittime 300 seconds, flags 0x100000001a, owning task 0x000001BD4AC2D468. Not continuing to wait.
Source spid19s
Message
Error: 845, Severity: 17, State: 1.Message
Time-out occurred while waiting for buffer latch type 3 for page (1:42954663), database ID 12.Message
A time-out occurred while waiting for buffer latch -- type 3, bp 000001BDA73F7740, page 1:42954663, stat 0xb, database id: 12, allocation unit Id: 72057622850764800, task 0x000001B109AE48C8 : 0, waittime 300 seconds, flags 0x100000001a, owning task 0x000001BD4AC2D468. Not continuing to wait.Message
One or more recovery units belonging to database 'ourdbname' failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure.
Message
A time-out occurred while waiting for buffer latch -- type 3, bp 000001BDC780B580, page 1:42954644, stat 0xb, database id: 12, allocation unit Id: 72057622850764800, task 0x000001BD4C043088 : 0, waittime 300 seconds, flags 0x100000001a, owning task 0x000001BD4AC0CCA8. Not continuing to wait.
Thanks
Answers
-
Hi,
Sounds like resource exhaustion...is disk or RAM getting hit here? What is the RAM in the server and also memory limit in SSMS? Does perfmon show memory or disk IO issues with queue or throughput?
Is this a 64 bit install of SQL?
Thanks,
Matt
- Marked as answer by msloy Friday, November 15, 2019 4:24 AM
-
Hi msloy
From your message, it seems you need use PSSDIAG to collect various information and then analysis them. I suggest you ask professional engineer for help, and they will deal with your problem separately and confidentially.https://support.microsoft.com/en-us/assistedsupportproducts.
Best regards,
CathyMSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com- Marked as answer by msloy Friday, November 15, 2019 4:24 AM
-
Frankly a lot of thing could be wrong or may be your hardware ( slow disk). I would start by asking what is output of select @@version. Before opening a MS case Let m see if I can give you any pointers. Could you please share output of sp_readerrorlog. Remove the sensitive and private contents.
Also how often you get this message. Do you get it when checkdb runs with index maintenance ?
Cheers,
Shashank
Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it
My TechNet Wiki Articles
MVP- Marked as answer by msloy Friday, November 15, 2019 4:24 AM
-
We recently got message in sql server log like below:
What could be wrong:
SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds
Common causes for the warnings on a dedicate SQL Server machine are an 1) unhealthy storage subsystem, 2) an unhealthy VM, or 3) an IO intensive workload (including non-SQL Server IO) that drastically exceeds the capabilities of the IO subsystem.
Dan Guzman, Data Platform MVP, http://www.dbdelta.com
- Marked as answer by msloy Friday, November 15, 2019 4:25 AM
-
This is SQL Server warning you have a serious problem with your disk storage. You must fix your disk storage problem. This is not something you resolve in SQL Server. SQL Server is reporting it is not performing due to an external disk IO issue, you must fix.
- Marked as answer by msloy Friday, November 15, 2019 4:25 AM
-
'SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [F:\MSSQL\LOG\MBAM Recovery and Hardware_Primary.ldf] in database id 10. The OS file handle is 0x0000000000001008. The offset of the latest long I/O is: 0x0000009d303600. The duration of the long I/O is: 32629 ms.'
This is a disk performance warning, check your disk and workload.
You need to run performance monitor at least, or pssdiag
https://sqlserver.code.blog/
- Edited by LiweiyinMicrosoft employee Friday, November 15, 2019 3:56 AM
- Marked as answer by msloy Friday, November 15, 2019 4:25 AM
All replies
-
Hi,
Sounds like resource exhaustion...is disk or RAM getting hit here? What is the RAM in the server and also memory limit in SSMS? Does perfmon show memory or disk IO issues with queue or throughput?
Is this a 64 bit install of SQL?
Thanks,
Matt
- Marked as answer by msloy Friday, November 15, 2019 4:24 AM
-
Hi msloy
From your message, it seems you need use PSSDIAG to collect various information and then analysis them. I suggest you ask professional engineer for help, and they will deal with your problem separately and confidentially.https://support.microsoft.com/en-us/assistedsupportproducts.
Best regards,
CathyMSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com- Marked as answer by msloy Friday, November 15, 2019 4:24 AM
-
Frankly a lot of thing could be wrong or may be your hardware ( slow disk). I would start by asking what is output of select @@version. Before opening a MS case Let m see if I can give you any pointers. Could you please share output of sp_readerrorlog. Remove the sensitive and private contents.
Also how often you get this message. Do you get it when checkdb runs with index maintenance ?
Cheers,
Shashank
Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it
My TechNet Wiki Articles
MVP- Marked as answer by msloy Friday, November 15, 2019 4:24 AM
-
We recently got message in sql server log like below:
What could be wrong:
SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds
Common causes for the warnings on a dedicate SQL Server machine are an 1) unhealthy storage subsystem, 2) an unhealthy VM, or 3) an IO intensive workload (including non-SQL Server IO) that drastically exceeds the capabilities of the IO subsystem.
Dan Guzman, Data Platform MVP, http://www.dbdelta.com
- Marked as answer by msloy Friday, November 15, 2019 4:25 AM
-
This is SQL Server warning you have a serious problem with your disk storage. You must fix your disk storage problem. This is not something you resolve in SQL Server. SQL Server is reporting it is not performing due to an external disk IO issue, you must fix.
- Marked as answer by msloy Friday, November 15, 2019 4:25 AM
-
-
'SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [F:\MSSQL\LOG\MBAM Recovery and Hardware_Primary.ldf] in database id 10. The OS file handle is 0x0000000000001008. The offset of the latest long I/O is: 0x0000009d303600. The duration of the long I/O is: 32629 ms.'
This is a disk performance warning, check your disk and workload.
You need to run performance monitor at least, or pssdiag
https://sqlserver.code.blog/
- Edited by LiweiyinMicrosoft employee Friday, November 15, 2019 3:56 AM
- Marked as answer by msloy Friday, November 15, 2019 4:25 AM