locked
A timeout occurred while waiting for buffer latch RRS feed

  • Question

  • Hi All,

    Anybody is aware how to resolve the below issue.

    Initially messages in the error log:

    SQL Server has encountered 10 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\MSSQLSERVER\data.mdf] in database id 6.  The OS file handle is 0x000000000000106C.  The offset of the latest long I/O is: 0x00000f02380000

    Followed by below error message:

    A time-out occurred while waiting for buffer latch -- type 2, bp 00000003EE809000, page 1:23189244, stat 0x40d, database id: 6, allocation unit Id: 72057595105640448, task 0x00000003B9F99C28 : 2, waittime 300 seconds, flags 0x3a, owning task 0x00000003B9AAB088. Not continuing to wait.

    A time-out occurred while waiting for buffer latch -- type 3, bp 00000003C4FFB700, page 2:0, stat 0xf, database id: 6, allocation unit Id: 6488064, task 0x00000003C3029468 : 0, waittime 300 seconds, flags 0x1000000019, owning task 0x00000003C3029468. Not continuing to wait.

    No dump files for these errors.

    Regards,

    kccrga


    -kccrga http://dbatrend.blogspot.com.au/


    • Edited by kccrga Friday, January 13, 2017 1:09 AM
    Friday, January 13, 2017 1:06 AM

Answers

  • Hi,

    I am on SQL SERVER 2014. Below is the output:

    Microsoft SQL Server 2014 (SP2-GDR) (KB3194714) - 12.0.5203.0 (X64)
     Sep 23 2016 18:13:56
     Copyright (c) Microsoft Corporation
     Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

    The VM backups were running around the same time. But it usually around the same time.

    SQL Server has encountered 3 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\MSSQLSERVER\data.mdf] in database id 6. 

    last target outstanding: 48, avgWriteLatency 0

    SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [H:\data\tempdb_01.ndf]

    There were around 10-30 messages saying I/O taking longer than usual.


    The reason for this message is VMbackup and when this is running it is causing I/O contention can you ask the VMware team to stop this for a day or two and see if this message is coming again

    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

    Friday, January 13, 2017 5:52 AM

All replies

  • The first message indicated that your disks are too slow. The second error message could mean that you are experiencing tempdb contention. How many processors do you have and how many tempdb files?

    Friday, January 13, 2017 1:19 AM
  • How many processors do you have and how many tempdb files?

     I got 8 processors and 3 TempDB data files. The database id in the timeout error is 6. How can we correlate to tempdb contention?


    -kccrga http://dbatrend.blogspot.com.au/

    Friday, January 13, 2017 1:26 AM
  • The rule of thumb is that you should have 8 tempdb files, all equally sized. To find out the database id do this:

    select db_name(6)

    use the query in this article to check for tempdb contention:

    http://www.sqlservercentral.com/blogs/robert_davis/2010/03/05/Breaking-Down-TempDB-Contention/?__hstc=81186632.54fb9ea46597101060833289151b5d1a.1484223319245.1484223319245.1484271636235.2&__hssc=81186632.1.1484271636235&__hsfp=1260068409

    Friday, January 13, 2017 1:41 AM
  • The processors are shared across multiple instances and that is the reason why kept the tempdb files equally sized and kept it to three. How many tempdb files can if the processors are shared across multiple instances? I have three instances running on the same server.


    -kccrga http://dbatrend.blogspot.com.au/


    • Edited by kccrga Friday, January 13, 2017 2:36 AM
    Friday, January 13, 2017 2:32 AM
  • Hi Hilary,

    If the IO requests taking longer than 15 seconds happens for databases other than tempdb. I executed the query in the above and found no issues. The DISK IO subsystem is on SSD which is very quick. I checked the IO pending requests and could not find any issues related to DISK IO using the below link.

    https://technet.microsoft.com/en-us/library/bb838723(office.12).aspx

    Regards,

    kccrga


    -kccrga http://dbatrend.blogspot.com.au/




    • Edited by kccrga Friday, January 13, 2017 2:45 AM
    Friday, January 13, 2017 2:43 AM
  • In that case the io message is likely transient and can be ignored. How many of these do you get per week?

    The same number of data files for tempdb per processor counts if you have a single instance or multiple instances. Note that you might be able to get away with less, but if you are running into tempdb contention  - a symptom of which is pagelatch waits on tempdb - you likely need more than you currently have.

    Friday, January 13, 2017 2:46 AM
  • Hi Hilary,

    I have got 50 messages saying I/O requests taking longer to complete this week. As you said it is transient and happens on different databases and pattern is not the same.

    Regards,

    kccrga


    -kccrga http://dbatrend.blogspot.com.au/

    Friday, January 13, 2017 3:02 AM
  • That is a fair amount. You definitely have disk slowness. How old is your server? You might not have the correct controller for the SSDs.
    Friday, January 13, 2017 3:14 AM
  • It relatively new server purchased before six months. How do we find if I have correct SSD controller?

    -kccrga http://dbatrend.blogspot.com.au/

    Friday, January 13, 2017 3:43 AM
  • If the hardware is that new it should support the SSDs.
    Friday, January 13, 2017 4:05 AM
  • Kccrga,

    The issue can occur because of many reasons, one could be slow disk, and you would have to first tell me what were you running when you got this message ?

    As of now I dont think tempdb has anything to do here

    What is output of select @@version

    You would have to show us complete errorlog so that we can check what was happening any backup job, checkdb or any other job running ?

    About time out error for latch please see

    https://mssqlwiki.com/tag/timeout-occurred-while-waiting-for-latch/


    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

    Friday, January 13, 2017 5:06 AM
  • Hi,

    I am on SQL SERVER 2014. Below is the output:

    Microsoft SQL Server 2014 (SP2-GDR) (KB3194714) - 12.0.5203.0 (X64)
     Sep 23 2016 18:13:56
     Copyright (c) Microsoft Corporation
     Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

    The VM backups were running around the same time. But it usually around the same time.

    SQL Server has encountered 3 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\MSSQLSERVER\data.mdf] in database id 6. 

    last target outstanding: 48, avgWriteLatency 0

    SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [H:\data\tempdb_01.ndf]

    There were around 10-30 messages saying I/O taking longer than usual.

    Regards,

    kccrga


    -kccrga http://dbatrend.blogspot.com.au/

    Friday, January 13, 2017 5:44 AM
  • Hi,

    I am on SQL SERVER 2014. Below is the output:

    Microsoft SQL Server 2014 (SP2-GDR) (KB3194714) - 12.0.5203.0 (X64)
     Sep 23 2016 18:13:56
     Copyright (c) Microsoft Corporation
     Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

    The VM backups were running around the same time. But it usually around the same time.

    SQL Server has encountered 3 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\MSSQLSERVER\data.mdf] in database id 6. 

    last target outstanding: 48, avgWriteLatency 0

    SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [H:\data\tempdb_01.ndf]

    There were around 10-30 messages saying I/O taking longer than usual.


    The reason for this message is VMbackup and when this is running it is causing I/O contention can you ask the VMware team to stop this for a day or two and see if this message is coming again

    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

    Friday, January 13, 2017 5:52 AM