One of my clients contacted me to resolve an issue where they were seeing these errors few minutes after restarting SQL Server Service. In this blog, we would learn about how to fix error Timeout occurred while waiting for latch: class FGCB_ADD_REMOVE.
As soon as I got on the call with them, my first question was where they are seeing the latch error and they showed me SQL Server ERRORLOG. Here are the messages in SQL Server ERRORLOG file.
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.
I checked documentation and found below: sys.dm_os_latch_stats (Transact-SQL)
As per above, latch class FGCB_ADD_REMOVE is related filegroups for ADD and DROP file operations. So, if SQL is performing an operation that will change the database files (add, remove, grow, shrink, rename) but they’re being blocked then we may see this.
I asked them to capture XEvents to capture more data. After analysis, I was able to see that SQL Server was trying to grow a database data file and was stuck waiting for the file to grow.
There are few things to check in such situation:
- Make sure that auto-growth setting is not set to high value. I always prefer to have it as a fixed size in MB rather than Percentage. I always go with 512 or 1024 MB auto-growth value.
- Make sure the disk subsystem is healthy. . Even if the size of the growth is small, due to disk slowness, it might take more time to complete the growth and might time out.
- Make sure that instant file initialization is enabled. In SQL Server, data files can be initialized instantaneously. Instant file initialization allows for fast execution of the previously mentioned file operations. Instant file initialization reclaims used disk space without filling that space with zeros. Refer my below articles.
- SQL SERVER – How to Check If Instant File Initialization Enabled or Not?
- SQL SERVER – How to Turn On / Enable Instant File Initialization?
In my client’s server, it was ca ombination of 1 and 3. We reduced the growth size and enabled Instant File Initialization. They never faced above-mentioned error after following my guidance.
Have you ever seen this kind of latch error in SQL Server?
Reference: Pinal Dave (https://blog.sqlauthority.com)