Troubleshooting - Msg 845 - Time-out occurred while waiting for buffer latch type 3

06 February,2016 by Jack Vamvas

Question: I received this error message when running a recreate index job on a SQL Server  database

Msg 845, Level 17, State 1, Line 2
Time-out occurred while waiting for buffer latch type 3 for page (1:66909436), database ID 371

There was a very heavy wokload on the system at the time.

Answer: A Msg 845 Time-out occurred while waiting for buffer latch type 3 refers to a timeout when a
SQL Server task is attempting to acquire a latch but it is currently acquired by another task.

The requesting task waits until the latch release. If the requesting task has to wait for longer than 5 minutes , the requesting task aborts.
Normally the process of requesting and releasing latches is very quick. So to receive a Msg 845 Time-out occurred while waiting for buffer latch type 3 indicates a serious performance problem.

Tyoe 3 refers to the update mode, if you'd like see a full list of latch modes, execute this sql code:

          select * from sys.dm_xe_map_values where name='latch_mode'

The symptoms are normally very slow or server hanging, timeouts of tasks , such as the example presented.

The best strategy to adopt is an overall system environment analysis.

1) IO requests of SQL Server cannot be met by the hardware.This becomes pronounced when there is a heavy workload on the system. For example, if you're running a batch processing job, full backups and index maintenace job , all in parallel - then there is a greater demand on IO channels.
If you think this may be the cause, then review the timings of these operations and schedule at separate times
Maybe when the server was first set up it was suitable for the workload and the workload has now grown?

SQL Performance tuning is about Asking the right question

2) OS and SQL Server configurations. Have you completed a thorough review of the configurations. Most configuration out of the box , tend to be suitable for most workloads, but there are a few useful ones to check. Read up on Instant file initialization

3) Hardware issues - request from the server administrator if firmware is up to date or any errors appearing in the logs

4) Query optimisastion - have the queries or indexes become inefficient , leading to increased IO demands

5) AutoGrow - Are there to many auto grow activities?SQL LOG FILE AUTOGROW performance troubleshooting - SQL ...

6) Storage devices - In a shared infrastructure it is common for underlying systems to shift , new storage neighbours may lead to increased competition for IO  throughput.

Read More on sql server latches

Warning: Failure to calculate super-latch promotion threshold - SQL ...

Identify Page Latch contention - SQL Server DBA

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Troubleshooting - Msg 845 - Time-out occurred while waiting for buffer latch type 3


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer