Environment

-> Environment has two (2) standalone database servers JBSAG1 and JBSAG2 with Always On configured. Below is the Always On dashboard.

-> Primary Replica JBSAG1 goes down and is expected to come online after several hours. Below is the Always ON dashboard,

-> Automatic failover is not enabled as Availability group is configured for Asynchronous commit. Availability group is in Resolving state.

-> Below error occurs when we try to remove the database JBFinance from Availability group JBAG from secondary replica,

The database ‘JBFinance’ failed to leave the availability group ‘JBAG’ on the availability replica ‘JBSAG2’. (Microsoft.SqlServer.Management.SDK.TaskForms)

The local availability replica of availability group ‘JBAG’ cannot accept signal ‘UNJOIN_DB’ in its current replica role, ‘RESOLVING_NORMAL’, and state (configuration is in Windows Server Failover Clustering store, local availability replica has joined). The availability replica signal is invalid given the current replica role. When the signal is permitted based on the current role of the local availability replica, retry the operation. (Microsoft SQL Server, Error: 41121)

Msg 41190, Level 16, State 8, Line 6
Availability group ‘JBAG’ failed to process remove-database command. The local availability replica is not in a state that could process the command. Verify that the availability group is online and that the local availability replica is the primary replica, then retry the command.

-> We cannot drop the database from availability group when it is in resolving state.

-> The option available are as below,

[$] Perform a “Failover with potential data loss”.

ALTER AVAILABILITY GROUP [JBAG] FORCE_FAILOVER_ALLOW_DATA_LOSS;

[$] Drop availability group JBAG and then recover the database.

USE [master]
GO
DROP AVAILABILITY GROUP [JBAG];
GO

restore database JBFinance with recovery
GO

-> We have 2 options as advised above. But I would always select the first option ” Failover with potential data loss” during scenarios like this.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.