Recently, one of my clients encountered an issue where they were seeing timeout errors shortly after restarting the SQL Server service. In this blog post, we will discuss how to fix the error “Timeout occurred while waiting for latch: class FGCB_ADD_REMOVE”.
When I got on a call with the client, my first question was where they were seeing the latch error. They showed me the SQL Server ERRORLOG file, which contained the following messages:
2018-09-25 10:32:00.74 spid423 Timeout occurred while waiting for latch: class 'FGCB_ADD_REMOVE', id 00000000807D59B8, type 2, Task 0x00000000C896FDC8 : 0, waittime 300, flags 0x1a, owning task 0x00000000820BE608. Continuing to wait. 2018-09-25 10:32:08.80 spid419 Timeout occurred while waiting for latch: class 'FGCB_ADD_REMOVE', id 00000000807D59B8, type 2, Task 0x00000000B2DB7948 : 0, waittime 300, flags 0x1a, owning task 0x00000000820BE608. Continuing to wait. 2018-09-25 10:32:09.81 spid444 Timeout occurred while waiting for latch: class 'FGCB_ADD_REMOVE', id 00000000807D59B8, type 2, Task 0x0000000082581288 : 0, waittime 300, flags 0x1a, owning task 0x00000000820BE608. Continuing to wait.
Upon further investigation, I found that the latch class ‘FGCB_ADD_REMOVE’ is related to filegroups for ADD and DROP file operations. If SQL Server is performing an operation that will change the database files (such as adding, removing, growing, shrinking, or renaming), but these operations are being blocked, we may encounter this error.
To resolve this issue, I recommended the following steps to my client:
- Make sure that the auto-growth setting is not set to a high value. It is advisable to have a fixed size in MB rather than a percentage. I suggested using a 512 or 1024 MB auto-growth value.
- Ensure that the disk subsystem is healthy. Even if the size of the growth is small, disk slowness can cause the growth operation to take longer and potentially time out.
- Verify that instant file initialization is enabled. Instant file initialization allows for fast execution of file operations, including the ones mentioned above. It reclaims used disk space without filling it with zeros.
In my client’s case, the issue was a combination of the first and third steps. We reduced the growth size and enabled Instant File Initialization. After following these recommendations, they no longer encountered the latch error.
If you have ever come across a similar latch error in SQL Server, I would love to hear about your experience. Feel free to share your thoughts in the comments section below.