Availability Group send or redo queue backlog

What this check looks for

Remote replicas, meaning is_local = 0, in sys.dm_hadr_database_replica_states where either queue is over 100 MB:

  • log_send_queue_size greater than 102400 KB
  • redo_queue_size greater than 102400 KB

The message reports both numbers in megabytes, for the named database on the named replica.

Why it matters

These two numbers are the only honest reading of the recovery point and recovery time you actually have. Not the ones in the disaster recovery plan, and not the ones the Availability Group dashboard implies by drawing a green tick.

The send queue is data that exists on the primary and has not reached this secondary yet. If the primary were lost right now, that is exactly what you would lose. A 2 GB send queue is a 2 GB data loss event waiting for a bad afternoon, and it does not matter that the replica reports itself as synchronizing.

The redo queue is log that has arrived at the secondary but has not been applied. On failover, the secondary has to work through all of it before the database comes online. That is your real failover time, and it is measured in whatever the redo rate happens to be, not in the number somebody wrote down when the group was designed.

A backlog on a synchronous replica is different again: there the primary is waiting for the secondary to harden every commit, so a struggling secondary shows up as slow commits on the primary. Users experience that as the application being slow, and nobody looks at the replica.

How to confirm it yourself

SELECT ag.[name]                          AS [availability_group],
       DB_NAME(drs.[database_id])         AS [database_name],
       ar.[replica_server_name],
       ar.[availability_mode_desc],
       drs.[synchronization_state_desc],
       drs.[log_send_queue_size] / 1024   AS [send_queue_mb],
       drs.[log_send_rate]      / 1024    AS [send_rate_mb_per_sec],
       drs.[redo_queue_size]    / 1024    AS [redo_queue_mb],
       drs.[redo_rate]          / 1024    AS [redo_rate_mb_per_sec],
       drs.[last_commit_time]
  FROM sys.dm_hadr_database_replica_states AS drs WITH (NOLOCK)
 INNER JOIN sys.availability_replicas AS ar WITH (NOLOCK)
         ON ar.[replica_id] = drs.[replica_id]
 INNER JOIN sys.availability_groups AS ag WITH (NOLOCK)
         ON ag.[group_id] = ar.[group_id]
 WHERE drs.[is_local] = 0
 ORDER BY drs.[redo_queue_size] DESC;

Divide the queue by the rate to get the number that matters. A 500 MB redo queue moving at 50 MB per second is ten seconds of failover. The same queue at 2 MB per second is four minutes, and that is the number to compare against what you promised.

How to fix it

A backlog is a symptom, and the cause is one of four things.

  1. The network between primary and secondary. Check the send rate rather than the queue. A send queue with a healthy send rate is a burst; a send queue with a low send rate is a link that cannot keep up with the workload. Bulk loads and index rebuilds generate far more log than normal activity and are the usual trigger.
  2. The secondary’s disk. Redo is a write workload on the secondary. If the secondary is on cheaper storage than the primary, which is extremely common, redo is where you find out.
  3. A long running read query on the secondary. Readable secondaries block redo when a query holds a schema stability lock on a table redo needs to change. Look for REDO_BLOCKED in sys.dm_exec_requests on the secondary.
  4. The workload itself. An index rebuild on a large table can generate more log in ten minutes than the rest of the day combined. Rebuilding online, in smaller pieces, or during a window when the link is quiet, all help.

If the backlog is persistent rather than a spike, the group’s configuration does not match the workload. That is a design conversation, not a fix.

How long it takes

About an hour and a half to establish which of the four it is. Fixing the underlying cause varies from a query change to new hardware.

Report Why you would go there
Availability Groups Every replica and its queues together, rather than one finding.
Recovery Exposure What the send queue means in data loss terms.
I/O by Drive Whether the secondary’s storage is the bottleneck.
Waits HADR_SYNC_COMMIT on a primary waiting for a synchronous secondary.
Index Fragmentation Whether index maintenance is what generates the log spikes.
Check
Availability Group database is not healthy A replica that has stopped moving altogether.
Log truncation is blocked What a persistent backlog eventually does to the primary’s log.
Reindexing during the day A common source of the log volume behind a backlog.

Frequently asked questions

Why 100 MB? It is a size at which the queue is no longer noise and is worth a look, without firing on every busy minute. The number to judge by is the queue divided by the rate, which the query above gives you.

The state says SYNCHRONIZED but there is a redo queue. That is normal and it is the point of this check. SYNCHRONIZED means the log reached the secondary and was hardened, not that it was applied. The redo queue is what is left.

Is a send queue on an asynchronous replica expected? Some, yes. Asynchronous means the primary does not wait. A persistently large one still means data loss on failover, which is the thing asynchronous mode trades away.

Nothing is listed but failover is still slow. Then look at the redo rate rather than the queue. A tiny queue moving very slowly is still slow, and so is a failover that spends its time on recovery rather than redo.