all my availability group are in resolving state on secondary

Cappelletti Alen 21 Reputation points
2021-09-14T10:13:24.947+00:00

Hi all, I have for several days the DBs/AG in this state (Not synchronizing / Recovery) on the secondary node.
The main node is active. I can't get out of it ... I would like to recreate the AGs but it doesn't make me delete anything on the secondaries.

How is it best to proceed? I tried to do a reboot on node 2 but nothing changes.
I'm stuck on it...

Thanks Alen

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,758 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. YufeiShao-msft 7,056 Reputation points
    2021-09-15T02:27:37.307+00:00

    Hi @Cappelletti Alen ,

    Since the server had been offline for a while, you can try applying the latest transaction logs on the database, to see if that would kick-start the recovery process: 

    --suspend data movement of the database in secondary  
alter database [test] set hard suspend   
  
--remove database from AG group  
alter databse [test] set hard off   
  
--restore the latest transaction logs in secondary node (before tempdb is filled up/database are not accessible)  
restore log [test] from disk = 'device_name'   
  
--re-join database to availability group  
alter database [test] set hard availability group = [SENetwork_AG]  
  
--resume the data movement  
alter database [test] set hard resume

    https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-hadr?redirectedfrom=MSDN&view=sql-server-ver15#examples