AG failover databases go into Initializing / in recovery
-
We did a failover of an AG and two databases went into Initializing / in recovery. This is on Win2019/SQL Server 2019. I issued the below two commands for each db on the secondary node that has the issue:
We have 2 other nodes for the AG so removing the db from the AG will cause an outage to queries on another node.
ALTER DATABASE [xxxxx] SET HADR OFF;
ALTER DATABASE [xxxxx2] SET HADR RESUME;
In Activity Monitor the Task state is SUSPENDED and Wait Time just grows. It is almost like a system process is blocking it but I don’t see that for sure. I canceled the first one after a couple of hours and it has been in Killed/Rollback forever. The second on has been in the suspended state for hours.
The other nodes are healthy and the databases are moving data for these specific databases just fine.
Any help is appreciated.
-
Using Set HADR OFF you're telling the engine to forget the whole content of the replica !
ref: HADR OFF
ref RESUME HADR
My guess is the engine is still trying to catchup processing the available log(s).
Check the scenario suggested with Set HADR OFF to get you back ontrack !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply