Post reply

A time-out occurred while waiting for buffer latch -- type 3 , type 4

  • July 9, 2019 at 10:04 am

    #3661056

    I had this issue with Microsoft SQL Server 2017 (RTM-CU14) (KB4484710) - 14.0.3076.1 (X64)  on Windows Server 2016 Standard 10.0 <X64> (Build 14393: )

    I killed the deadlocks with no effect (rolling back forever) and the only thing that worked was a restart..

    But I can't realize how this problem originates, knowing that I have a newer and almost updated version of SQL Server

    Here are the errors:

    Message

    A time-out occurred while waiting for buffer latch -- type 3, bp 0000013412CB2E80, page 6:1293953, stat 0x10f, database id: 6, allocation unit Id: 72057598418223104, task 0x0000012E2C07ACA8 : 0, waittime 300 seconds, flags 0x1000000039, owning task 0x00000133BAAC24E8. Not continuing to wait.

    Failed:(-1073548784) Executing the query "BACKUP LOG [DB] TO DISK = N'\\\\xxx\\yyy\\DB..." failed with the following error: "Time-out occurred while waiting for buffer latch type 3 for page (6:1293953), database ID 6.

    BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    One or more recovery units belonging to database 'DB' failed to generate a checkpoint.

    This is typically caused by lack of system resources such as disk or memory,

    Examine previous entries in the error log for more detailed information on this failure.

    Msg 845, Level 17, State 1, Line 12

    Time-out occurred while waiting for buffer latch type 3 for page (6:65538), database ID 6.

    So:

    Why?

    What explanation is valid for SQL Server 2017 CU14?

    The Disk, RAM, CPU was ok , the only thing is that I applied more RAM recently.

    Can anyone please help me?

    I found this posts,but none of them seems to describe the situation exactly (older versions of SQL Server, older sp ...)

    https://itknowledgeexchange.techtarget.com/sql-server/tempdb-latch-timeouts-with-lots-of-ram/

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/7320d4bc-2bd8-4f8f-9920-ad23b4f509c7/sqlserver-2016-a-timeout-occurred-while-waiting-for-buffer-latch-type-3-and-message-a-timeout?forum=sqldatabaseengine

    Maybe this one fits a bit because I have 72 and 29 MS avg write stall on my DB server .... It is strange because it should be a SSD disk on the VM.

    https://social.msdn.microsoft.com/Forums/en-US/bd439f0f-6b57-4c18-bbd9-ec6a86c20b0b/sql-server-memory-and-cache-issues?forum=sqlkjmanageability

    I have only the checkbox on Always On Availability Groups

     

  • July 9, 2019 at 11:24 am

    #3661074

    I don't have an answer, only some questions.

    Have you looked at the wait statistics? Specifically for the queries involved? What are they waiting on (yeah, obviously the buffer latch, but what else). Talk to the VM team. Are they virtualizing storage? Those write stalls are stupid long. I get it that one of the queries is a backup, so no tuning opportunities there. However, what does the other query look like? Execution plan? What's it doing? How is it doing it?

    Oh, and what you're seeing is not called a deadlock. In a deadlock, one of the process would be chosen as a deadlock victim and rolled back. What you're seeing is just called blocking.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply