Sample Header Ad - 728x90

Database both joined and not joined to an AG

1 vote
0 answers
292 views
I have a Distributed Availability Group (DAG) from cluster *Main* to cluster *Alt* - both with two replicas. The *Main* is the primary and *Alt* is a forwarder One (of many) databases called *MyDb* in the AG on the forwarder side's secondary replica was in a bad state and was dropped. I've used automatic seeding to bring back the database but it failed with a failure_state_desc = SQL Error Now the database is stuck in RESTORING state and the error log says: > Starting up database 'MyDb'. > The database 'MyDb' is marked RESTORING and is in a state that does not allow recovery to be run. When I run
ALTER DATABASE [MyDb] SET HADR AVAILABILITY GROUP = [fwdAg];
I get an error > Msg 41145, Level 16, State 1, Line 46 > Cannot join database 'MyDb' to availability group 'fwdAg'. The database has already joined the availability group. This is an informational message. No user action is required. When I try
ALTER DATABASE [MyDb] SET HADR resume;
I get error > Msg 35242, Level 16, State 16, Line 50 > Cannot complete this ALTER DATABASE SET HADR operation on database 'MyDb'. The database is not joined to an availability group. After the database has joined the availability group, retry the command. which contradicts the previous error. I get the same error if I try
ALTER DATABASE [MyDb] SET HADR off;
And when I query the DMVs
select
	adc.database_name as dbName
    , ag.name as agName
    , drs.synchronization_state_desc
from sys.availability_databases_cluster as adc
join sys.availability_groups as ag
    on adc.group_id = ag.group_id
left join sys.dm_hadr_database_replica_states AS drs
    on drs.group_id = adc.group_id 
    and drs.group_database_id = adc.group_database_id
where ag.name = 'fwdAg'
I get back the *agName* but not the database_replica_states info. How do I re-add the DB to the AG on the secondary (forwarder side)? I'm on SQL Server version: Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2022 Datacenter 10.0 (Build 20348: ) (Hypervisor)
Asked by Zikato (5724 rep)
Sep 6, 2024, 05:00 PM
Last activity: Feb 20, 2025, 07:44 AM