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

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... Read more →

How to troubleshoot Flush Cache error message in SQL Server Logs

Question: I’m getting a Flush Cache message in the SQL Server Logs. FlushCache: cleaned up 3606 bufs with 476 writes in 69033 ms (avoided 4 new dirty bufs) for db 7:0 What does it mean regarding sql server performance? Are there methods I should be using to troubleshoot this message Answer: The impact on SQL Server performance will depend on understanding the context . Checkpoint logging causes this message to appear in the SQL Server logs. Just to be specific, these messages only started appearing in the logs since 2012. Prior to 2012 you needed to turn on a trace... Read more →

Fix: –NESTING_TRANSACTION_FULL wait type and how to reduce it

Question: There was a failure on a regular SQL Server job. There are now repeated errors focused on NESTING_TRANSACTION_FULL. Double checked all usual resource indicators such as CPU,IO,Memory and all appear to be within normal boundaries Timeout occurred while waiting for latch: class 'NESTING_TRANSACTION_FULL', I checked the waiting tasks and discovered the wait type as LATCH_EX with resource description as : NESTING_TRANSACTION_FULL (0000000E64CA80F8) Answer:This is a tough nut to crack. I’ve experienced this problem in the past under different circumstances. These are some of the methods I’ve used to solve or alleviate the symptoms. Without full understanding whether it was... Read more →

Warning: Failure to calculate super-latch promotion threshold

Question: Why is the warning message Failure to calculate super-latch promotion threshold appearing in the SQL Server Error Log files? Answer: First some background information. If thousands of queries are accessing a lookup value table in the data cache it’s refered as a hot page. These hot pages can experience latch contention. A sustained high level of SH activity can be promoted to a sub-latch. (sub-latch is an interchangeable term with super-latch). The sub-latch is a single-latch scaled into a latch array per logical CPU. The worker can access just the sub-latch assigned to the related scheduler. Acquiring the sub-latch... Read more →

How to Identify Page Latch contention

At a certain point latches could begin to block as one IO makes a request which hinders the response of another IO. Read more →