SQL SERVER – Timeout Occurred While Waiting for Latch: Class FGCB_ADD_REMOVE

SQL
No Comments

SQL SERVER - Timeout Occurred While Waiting for Latch: Class FGCB_ADD_REMOVE warning 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.

Solarwinds

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.

WORKAROUND/SOLUTION

There are few things to check in such situation:

  1. 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.
  2. 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.
  3. 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.

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)

Solarwinds
, , , , , , ,
Previous Post
Azure Virtual Machine – You Must Change Your Password Before Logging On The First Time
Next Post
SQL SERVER – Always On AG – HADRAG: Did not Find the Instance to Connect in SqlInstToNodeMap Key

Related Posts

Leave a Reply

Menu