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?
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