4

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: enter image description here

enter image description here

Failover ends: enter image description here

All in all

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

  • Could you add the sql server version(s), and if possible the sql server error log files from the entire timeframe? (on a pastebin or something similar maybe) – Randi Vertongen Feb 26 at 8:23
  • Hi, I added version information and sql error log parts. I see that you also had a similar issue a couple of days ago here:dba.stackexchange.com/questions/230128/… – Bahtiyar Samet Çoban Feb 26 at 8:52
  • You are correct, difference is that I am on version: 13.0.5026.0. Very interesting to see that others are having the same problem. You are able to failover after a while however? I failed back (killed session) after a couple minutes. Do you also get both AG's in recovery pending while the issue is happening? I had the same issue where 2 of my 8 db's where in recovery / reverting. – Randi Vertongen Feb 26 at 9:08
  • 1
    I searched on internet but couldn't find any related or similar case, yours is the only close one. On the other hand, the difference between our cases is that I am able to failover successfully in seconds.Thus, I don't have outages or down services. My problem is related to secondary database only after failover, it takes 30+ minutes to finish redo/undo phase which creates a vulnerability against a possible problem which may occur in that time frame. In that scenario I am not being able to fail back and that would result as an outage. – Bahtiyar Samet Çoban Feb 26 at 9:23
  • 1
    Im adding a bug report, at feedback.azure.com/forums/908035-sql-server/suggestions/… – Randi Vertongen Feb 26 at 9:45
3

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

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

1

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.

-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

  • 1
    You need to provide more details as to why this is useful in this case. To me this is an incorrect answer. Please read what the OP is asking instead of just posting commands without any explanation. – Kin Shah Feb 26 at 1:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.