none
error in the log RRS feed

  • 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:0

    Message
    average writes per second:  33.60 writes/sec 
                average throughput:   0.32 MB/sec, I/O saturation: 100, context switches 332

    SQL 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 0

    Source Server

    Message
    Long Sync IO: Scheduler 3 had 1 Sync IOs in nonpreemptive mode longer than 1000 ms

    Message
    FlushCache: cleaned up 24141 bufs with 16849 writes in 181911 ms (avoided 25301 new dirty bufs) for db 12:0

    Message
    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 0

    Message
    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

    Wednesday, November 13, 2019 11:38 PM

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
    Thursday, November 14, 2019 12:15 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,
    Cathy 

    MSDN 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
    Thursday, November 14, 2019 9:53 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
    Thursday, November 14, 2019 11:22 AM
    Moderator
  • 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
    Thursday, November 14, 2019 11:36 AM
    Moderator
  • 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
    Thursday, November 14, 2019 1:11 PM
    Moderator
  • '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/


    Friday, November 15, 2019 3:56 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
    Thursday, November 14, 2019 12:15 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,
    Cathy 

    MSDN 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
    Thursday, November 14, 2019 9:53 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
    Thursday, November 14, 2019 11:22 AM
    Moderator
  • 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
    Thursday, November 14, 2019 11:36 AM
    Moderator
  • 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
    Thursday, November 14, 2019 1:11 PM
    Moderator
  • Thanks all. This is a SQL server 2017 server. 

    Thanks

    Thursday, November 14, 2019 4:24 PM
  • '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/


    Friday, November 15, 2019 3:56 AM