SQL Server problems

Always On Availability Group Lag: Measure the Send and Redo Queues

22 November 2026

A secondary replica falls behind in one of two places. Log that the primary has not sent yet is data you lose in a failover. Log that the secondary received and has not applied yet is the time a failover takes. One query on the primary measures both for every database and every replica.

The two queues

SELECT ag.name                       AS ag_name,
       ar.replica_server_name        AS replica,
       DB_NAME(rs.database_id)       AS database_name,
       ar.availability_mode_desc     AS mode,
       rs.synchronization_state_desc AS state,
       rs.is_suspended, rs.suspend_reason_desc,
       rs.log_send_queue_size        AS send_queue_kb,
       rs.log_send_rate              AS send_kb_s,
       rs.redo_queue_size            AS redo_queue_kb,
       rs.redo_rate                  AS redo_kb_s,
       rs.secondary_lag_seconds      AS lag_s
FROM   sys.dm_hadr_database_replica_states rs
JOIN   sys.availability_replicas ar
       ON ar.replica_id = rs.replica_id
JOIN   sys.availability_groups ag
       ON ag.group_id = rs.group_id
ORDER  BY rs.log_send_queue_size DESC;
ColumnHow to read it
send_queue_kbLog of the primary that this secondary does not have yet. On an asynchronous replica it is what a forced failover loses.
redo_queue_kbLog the secondary has on disk and has not applied. Divided by redo_kb_s it is the seconds the database needs before it can open after a failover. A report on a readable secondary is that far behind.
lag_sHow many seconds the secondary is behind the primary.
stateSYNCHRONIZED for a synchronous replica that is up to date, SYNCHRONIZING for an asynchronous one or one that catches up, NOT SYNCHRONIZING when no log moves.
is_suspended1: data movement is stopped for this database, and suspend_reason_desc says by whom.

Is the replica connected and healthy

SELECT ar.replica_server_name  AS replica,
       st.role_desc, st.connected_state_desc,
       st.synchronization_health_desc,
       st.last_connect_error_description
FROM   sys.dm_hadr_availability_replica_states st
JOIN   sys.availability_replicas ar
       ON ar.replica_id = st.replica_id;

A replica that shows DISCONNECTED receives nothing. Its send queue is everything the primary wrote since, and the primary cannot truncate that log.

Which queue grows, and why

A redo that waits behind a query shows on the secondary:

SELECT session_id, command, blocking_session_id,
       wait_type, wait_time / 1000 AS wait_s
FROM   sys.dm_exec_requests
WHERE  command = 'DB STARTUP'
AND    blocking_session_id <> 0;

The fix, in order

  1. Resume what is suspended. ALTER DATABASE [Sales] SET HADR RESUME; on the replica where the database is suspended, after the reason in suspend_reason_desc is gone.
  2. Reconnect the replica. last_connect_error_description names the reason: the service on the secondary, the mirroring endpoint, the network or a certificate.
  3. Free a blocked redo. End the report that blocks it with KILL on the secondary. The redo goes on at once.
  4. Spread the log bursts. Rebuild indexes one at a time and load in batches, so the secondary receives what it can apply.
  5. Give the secondary the disks of the primary. A secondary on slower storage falls behind in every busy hour and never has the time to catch up.
  6. Watch the primary's log. Log that a secondary has not received cannot be truncated: log_reuse_wait_desc shows AVAILABILITY_REPLICA, and the next stop is error 9002.
  7. Decide on a synchronous replica that slows commits. Every commit waits until a synchronous secondary has written the log, which shows as the wait type HADR_SYNC_COMMIT. Repair the secondary, or change that replica to asynchronous commit until it is repaired, knowing that this gives up zero data loss for it.

See it coming

Lag is normal for seconds and a risk for minutes, and what counts as normal differs for every replica. Radar Engine, the detection layer of Scops, measures each secondary against its own usual lag, with no agent on the server:

DetectorWhen it fires
Availability group lagOnly when the lag is above 3 times the usual lag of that replica and growing. Watch from 3 minutes, Warning from 10 minutes, Critical from an hour. Warning too: 1 GB of log not yet sent.
Availability group healthA replica that is disconnected: Critical. A replica that is not healthy: Warning. A database on a synchronous replica that is not synchronized, or whose data movement is suspended: Warning.
FailoverThe primary of a group changed in the last 24 hours. A Watch, told for a day.
Transaction log fillWatch from 80% and rising. Warning when full within 6 hours, Critical when full within 30 minutes.

The finding names the group, the replica and the databases, since when, and on an asynchronous replica how much log would be lost if the primary failed now. One group is reported once, by its primary, however many of its replicas Scops monitors.

Questions

What is the log send queue in an availability group?
The log of the primary database that has not been sent to a secondary yet, in kilobytes, in log_send_queue_size of sys.dm_hadr_database_replica_states. On an asynchronous replica it is the data lost in a forced failover.

What is the redo queue?
The log a secondary has received and written and has not yet applied to its data files. It decides how long a failover to that replica takes and how far behind a readable secondary reads.

How do I measure availability group lag?
Query sys.dm_hadr_database_replica_states on the primary: secondary_lag_seconds is the lag in seconds, log_send_queue_size and redo_queue_size are the two queues, and the rates next to them tell how fast each one empties.

Can a lagging secondary slow the primary?
A synchronous one can: every commit waits until that secondary has written the log, which shows as HADR_SYNC_COMMIT waits. Any secondary that stops receiving also keeps the primary's log from being truncated.

Related problems

Sources