question

CappellettiAlen-4296 avatar image
0 Votes"
CappellettiAlen-4296 asked Yufeishao-msft commented

all my availability group are in resolving state on secondary

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-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

After the restart...
something stated to change...
now I see son AG synchronized... and only one Restoring.

I think could be a queue of t-log that are appling... I hope

Alen

0 Votes 0 ·

1 Answer

Yufeishao-msft avatar image
0 Votes"
Yufeishao-msft answered Yufeishao-msft commented

Hi @CappellettiAlen-4296,

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://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-hadr?redirectedfrom=MSDN&view=sql-server-ver15#examples


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thanks for your reply but everything went fine by itself, i had to wait 3 hours for synchronization (after restart of node naturally) and during this time I checked lags with this query... and saw second number column lowering a little at a time until get to 0.


 ;WITH 
     AG_Stats AS 
             (
             SELECT AR.replica_server_name,
                    HARS.role_desc, 
                    Db_name(DRS.database_id) [DBName], 
                    DRS.last_commit_time
             FROM   sys.dm_hadr_database_replica_states DRS 
             INNER JOIN sys.availability_replicas AR ON DRS.replica_id = AR.replica_id 
             INNER JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id 
                 AND AR.replica_id = HARS.replica_id 
             ),
     Pri_CommitTime AS 
             (
             SELECT    replica_server_name
                     , DBName
                     , last_commit_time
             FROM    AG_Stats
             WHERE    role_desc = 'PRIMARY'
             ),
     Sec_CommitTime AS 
             (
             SELECT    replica_server_name
                     , DBName
                     , last_commit_time
             FROM    AG_Stats
             WHERE    role_desc = 'SECONDARY'
             )
 SELECT p.replica_server_name [primary_replica]
     , p.[DBName] AS [DatabaseName]
     , s.replica_server_name [secondary_replica]
     , DATEDIFF(ss,s.last_commit_time,p.last_commit_time) AS [Sync_Lag_Secs]
 FROM Pri_CommitTime p
 LEFT JOIN Sec_CommitTime s ON [s].[DBName] = [p].[DBName]


;Alen

0 Votes 0 ·