Log truncation issue in AlwaysOn AG

Question

We have four nodes in our AG. After the weekly index maintenance job, the log drive became full. We enabled CDC in one of our database, so we disabled CDC to truncate the log. Then we ran 'checkpoint' and took log backup. But we were not able to truncate the log file. The log_reuse_wait_desc status of the database shows AVAILABILITY_REPLICA. We don't see anything in log send and redo queues.

Is there another solution to fix this issue instead of removing the DB from the AG?

---

Upon troubleshoot suggestion, I have checked everything. There is no blocking in all three secondary nodes. The last_redone_time and redo_queue_size are all in sync with primary replica.

I am not able to suspend and resume the data movement on working days for the time being since it is heavily being utilized. I have checked the log_reuse_wait status of the database, and it is still showing 'AVAILABILITY_REPLICA':

And the loginfo shows that 99% of the log is used.

Answer 1

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:

1. Try Suspend data movement and Resume for particular database
2. Perform LOG backup, check if log_reuse_wait_desc changed

If the AVAILABILITY_REPLICA not changed

1. Probably one of the replica still got the redo_block which you can identity from effected Replica's Extended Events -> Sessions -> "AlwaysOn_health"
   

2. 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, 
        state_desc, 
        recovery_model_desc, log_reuse_wait_desc,
        Rep.replica_server_name,
        rep.endpoint_url,
        DBRepStats.is_primary_replica,
        DBRepStats.synchronization_health_desc,
        DBRepStats.database_state_desc,
        (redo_queue_size / 1024.0) as redo_queue_size_MB,
        last_redone_time,
        last_redone_lsn

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

3. 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.
4. Once the blocking process killed, or completed. You can notice that the last_redone_time and 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