Most probably this issue due to network latency or long running transaction on primary replica. Thus, in production environment it's always better to have dedicated NIC in each replica that only handles Database Mirroring end-points communication.
However, in your case following steps may help:
- Try Suspend data movement and Resume for particular database
- Perform LOG backup, check if
AVAILABILITY_REPLICA not changed
Probably one of the replica still got the
redo_block which you can identity from effected Replica's Extended Events -> Sessions -> "AlwaysOn_health"
To confirm the blocking is the cause, you can execute following query, look at
last_redone_time this would indicate since when the replica got the backlog.
select db.name, db.database_id,
ag.name as GroupName,
(redo_queue_size / 1024.0) as redo_queue_size_MB,
from sys.databases as db
left outer join sys.availability_databases_cluster as AGDB on db.group_database_id = AGDB.group_database_id
left outer join sys.dm_hadr_database_replica_states as DBRepStats on db.group_database_id = DBRepStats.group_database_id
left outer join sys.availability_replicas as Rep on DBRepStats.group_id = Rep.group_id and DBRepStats.replica_id = Rep.replica_id
left outer join sys.availability_groups as AG on DBRepStats.group_id = AG.group_id
where db.database_id > 4
Query Result - Replica got more than a hour back-log
- By this time, if you look blocking within secondary replica, you may find some active process which causing the block. Most probably that would be read operation trying to block the things to make sure the schema (
SCH_M) of the object can’t be changed by DDL activity that been pushed by Primary replica.
- Once the blocking process killed, or completed. You can notice that the
redo_queue_size in sync with primary replica. Also,
AVAILABILITY_REPLICA should disappeared when you query
select name, log_reuse_wait_desc from sys.databases`
All Databases are in-sync (arrow marked was the one back-logged):
If nothing works, you already got the way to do force fix:
Remove Back-logged Replica from AG and Re-join, but there is impact on read-only routing of Availability Group