1

That's a weird scenario. The transaction log is full. It waits on AG. AG is all good. No latency, last commit/hardened time is almost real-time on all the nodes. The Estimated Recovery Time is zero. I checked this from the primary node and secondaries as well (in case if Dashboard is not updating). This page lists only two reasons for this error, a delivery latency and redo latency. Nothing applies to my system. How to troubleshoot this further?

Edit 1. The only change we have done to the environment, we added two SQL Server 2019 nodes in preparation for migration. It shouldn't follow to such problem though.

Edit 2. The only workaround I found is to re-add the DB to the AG.

2
  • 1
    Can you explain more about the recent change in AG configuration? Also, what do you mean by "The only workaround I found is to re-add the DB to the AG."? The database showing AVAILABILITY_REPLICA waits isn't part of the AG when you're seeing those waits??!?
    – AMtwo
    Nov 8, 2021 at 17:38
  • We added two SQL Server 2019 nodes to the AG. They work in asynchronous mode and are excluded from failover. The database is a part of the AG. The transaction log is full and the reason is AVALABILITY_REPLICA. It means that SQL Server thinks that these log records are still required on one of the secondaries. That's not the case, but we can fix it if we'll remove the DB from the AG and add it again. In this case, all secondaries will get a new copy of the DB and there should be no issues with log reuse. Yep, it works.
    – Alsin
    Nov 9, 2021 at 15:15

1 Answer 1

5

You should check the DMVs to confirm that the AG is healthy with send & redo queues. The dashboard in SSMS does not do a great job at representing the true send & redo health.

As part of my open-source DBA database I have a Check_AGHealth stored procedure that you can use to give more information.

Without using the full stored procedures, a couple of the important statements to run on primary would be these:

Check dm_hadr_database_replica_states

This query returns a bunch of data, but the interesting things you'll want to look for are:

  • Anything reporting that it is suspended
  • Anything reporting it is unhealthy
  • Large log send queue
  • Large redo queue

The ORDER BY clause on this should bubble those things up to the top of the result set, but any of those things would present a problem to clearing the log and would show a log reuse wait of AVAILABILITY_REPLICA

SELECT ServerName         = ar.replica_server_name, 
        AgName             = ag.Name,
        DbName             = db_name(ds.database_id), 
        AgRole             = rs.role_desc,
        rs.role ,
        UnsentLogMB        = ds.log_send_queue_size/1024.0,
        RedoQueueSizeMB    = ds.redo_queue_size/1024.0,
        RedoEstCompletion  = CASE WHEN redo_rate = 0 THEN 0 ELSE ds.redo_queue_size/ds.redo_rate END,
        SynchState         = ds.synchronization_state_desc, 
        AgHealth           = ds.synchronization_health_desc, 
        SuspendReason      = ds.suspend_reason_desc, 
        SynchHardenedLSN   = ds.last_hardened_lsn,
        LastHardenedTime   = ds.last_hardened_time,
        LastRedoneTime     = ds.last_redone_time,
        LastCommitTime     = ds.last_commit_time
FROM sys.availability_groups AS ag
JOIN sys.availability_replicas AS ar ON ar.group_id = ag.group_id 
JOIN sys.dm_hadr_database_replica_states AS ds ON ds.group_id = ar.group_id AND ds.replica_id = ar.replica_id
JOIN sys.dm_hadr_availability_replica_states AS rs ON rs.replica_id = ds.replica_id
ORDER BY 
    CASE 
        WHEN ds.suspend_reason_desc IS NOT NULL THEN 1
        WHEN ds.synchronization_state_desc <> 'HEALTHY' THEN 1
        ELSE 9
    END,
    rs.role DESC,
    ds.log_send_queue_size + ds.redo_queue_size DESC
;
1
  • Hi AMtwo. Thank you for the query. Nothing is suspended. Everything is healthy. No big queues. And mostly no queues at all, because no activity on the box. Yep, AG appears to be in order. I think the reason for this is two SQL Server 2019 nodes we added to AG before migration. It doesn't explain this issue, but that's the only change.
    – Alsin
    Nov 8, 2021 at 16:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.