Failover of the availability group 'dbag' to the local replica failed because the availability group resource did not come online due to a previous error

Question

I have two node SQL Server 2019 enterprise evaluation Read Scale Availability Groups
( Cluster less AGs in redhat linux 8)

during Forced manual failover with data loss
when the primary replica isn't available and can't be recovered.

To force failover with data loss, I have connect to the secondary SQL Server instance and run the following command:

ALTER AVAILABILITY GROUP [dbag] FORCE_FAILOVER_ALLOW_DATA_LOSS;

secondary replica become primary & it is working perfectly.

When the previous primary replica recovers, it will also assume the primary role. To ensure that the previous primary replica transitions into a secondary role I have run the command on the previous primary replica.

ALTER AVAILABILITY GROUP [dbag] SET (ROLE = SECONDARY);

it is showing below error:
Msg 41104, Level 16, State 5, Line 43
Failover of the availability group 'dbag' to the local replica failed because the availability group resource did not come online due to a previous error. To identify that error, check the SQL Server error log, cluster logs and system event logs. For information about how to view events and logs for a Windows Server Failover Clustering (WSFC) cluster, see Windows Server documentation.

How to resolve the issue.

Answer 1

Hi @Md. Solaiman ,

1.Please refresh your AG and then test again;
2.Try to install the latest CU and then test again.

If the issue still exists:
Please vote here: 40058803-manual-failover-without-data-loss-not-working-in-s

Or you can choose to ask professional engineers for help, and they will deal with your problem separately and confidentially: assistedsupportproducts

More information: fix-manual-failover-between-forwarder-and-secondary-replica-fails-with

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

I have update sql version. Now version is
Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) - 15.0.4073.23 (X64) Sep 23 2020 16:03:08 Copyright (C) 2019 Microsoft Corporation Enterprise Evaluation Edition (64-bit) on Linux (Red Hat Enterprise Linux 8.2 (Ootpa)) <X64>

but problem is not resolve.

ALTER AVAILABILITY GROUP [dbag] SET (ROLE = SECONDARY);

it is showing same below error:
Msg 41104, Level 16, State 5, Line 43
Failover of the availability group 'dbag' to the local replica failed because the availability group resource did not come online due to a previous error. To identify that error, check the SQL Server error log, cluster logs and system event logs. For information about how to view events and logs for a Windows Server Failover Clustering (WSFC) cluster, see Windows Server documentation.

Please help

Answer 3

I get the same error when I try it on my read-scale AG at home. It can very well be that this command only works in a "real" AG. I a cluster-less AG you are supposed to use FORCE_FAILOVER_ALLOW_DATA_LOSS, because there is not really any way to perform a failover with guarantee against data loss.

Let me also note that if you look at the documentation for ALTER AVAILABILITY GROUP, https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-availability-group-transact-sql?view=sql-server-ver15, this SET option is not listed in the syntax. So this appears to be a undocumented and unsupported command.