Availability Group database stays in Reverting for too long after a failover

Question

Architecture: I have 2 Node Sync-Commit AlwaysOn configuration running on Multi-Subnet Failover Cluster. Primary node is in Europe and Secondary node is in US. I have only one database in the Availability Group which is OperationsManager db of SCOM.

• Primary and Secondary Hosts are identical.
• SQL Server Version on both boxes: 13.0.5237 & Windows Core
• Update: I patched both servers to 10.0.5270.0 , it didn't help.
• DB VLF count is only 27.

Problem: When I initiate a failover, database fails over from Primary to Secondary node successfully in seconds. However, new secondary(old primary) database goes into Reverting / In Recovery phase and stays there for 30 minutes approximately.I also experienced same thing while failing back to original primary box so it's a problem which occurs both ways.

Findings: I searched about this on internet and read documentation to investigate the issue. When role change from Primary to Secondary is finished, new secondary database goes through 3 phases:

Synchronization State: “NOT SYNCHRONIZING” ; Database State: ONLINE

Synchronization State: “NOT SYNCHRONIZING” ; Database State: RECOVERING

Synchronization State: “REVERTING” ; Database State: RECOVERING

In my case, all time has spent on last step. I also monitored the undo process by looking into perfmon counter "SQLServer:Database Replica Log remaining for undo"

I checked primary site before failover tests to spot any long running transactions or open transactions but couldn't find one. After failover, "Log remaining for undo" was around 30MB and it took 30 minutes for secondary database to go back to "Synchronized" state. When taking into consideration that we are running in Sync-Commit mode and there is a little workload on primary, it shouldn't take 30 minutes for redo phase imho.

SQL Server Error Log: I found this strange messages.

• Remote harden of transaction 'RECEIVE MSG' (ID 0x000000004d52c65a 0001:01c4e415) started at Feb 22 2019 2:55PM in database 'OperationsManager' at LSN (2558:107841:1) failed.

• Remote harden of transaction 'GhostCleanupTask' (ID 0x000000004d6d15aa 0001:01c4eaa0) started at Feb 22 2019 2:59PM in database 'OperationsManager' at LSN (2558:107843:46) failed.

Failover starts:

Failover ends:

All in all

Have you ever seen this problem before? Do you have any recommandations?

Answer 1

One thing to check whenever database recovery is running long, whether it's a normal restore or an AG failover, is your VLF count. Having lots of VLFs (thousands or tens of thousands), or VLFs of unusual size (one or two extremely large VLFs) will cause this process to slow to a crawl.

Run the following command on your the database in question:

USE YourDatabaseName;
GO

DBCC LOGINFO;

Note: if you're on SQL Server 2016 SP2 or newer, you can use this dynamic management function instead of the DBCC command: sys.dm_db_log_info

The number of rows that comes back is the number of VLFs you have. If that number is very large, or if the FileSize column shows extreme outliers among your VLFs, then you can likely resolve the slow recovery problem by (at a high level):

1. shrinking the log file as small as possible
2. growing it back out to its target size
3. making sure autogrowth is set to a reasonable number based on your typical log growth rate and the frequency of your transaction log backups

The details of fixing VLF sizing issues have been covered extensively elsewhere, here is one example: A Busy/Accidental DBA’s Guide to Managing VLFs

Answer 2

As answered already VLF would be my primary choice as well to start with. Another thing I would consider is to look on the infra matching between 2 nodes.

Yes I know those are things to consider before setting up your server to be build ready. But it happens sometimes like in our case for one of the scenarios other node had an entire different storage system provided compared to it's primary replica node. We had SSDs on primary replica while SAN storage on secondary replica as that was a miss from storage team and hence when we were doing failover they seem to be taking some time.

Best bet collect all the performance metrics and try comparing between 2 replicas to find if all looks good. Not mandatory to be same, but good to have if you are going DR test and running load from other data center or new primary replica after AG failover

Answer 3

We have same issue with client's database and root cause was large amount of data from clients using FreeBCP to bulk insert their data. Our workaround solution was turn off bulk insert before manual alwayson failover.

Answer 4

This code always works for me in the similar situation:

ALTER DATABASE [Your Database] SET HADR SUSPEND;

GO

ALTER DATABASE [Your Database] SET HADR RESUME;

GO