SQL Server AG send and redo queues growing: replication lag and failover RTO

Two queues decide whether your Always On Availability Group can actually fail over: the send queue (log generated on the primary but not yet shipped to the secondary) and the redo queue (log received by the secondary but not yet replayed). When either grows without bound, replication lag is the visible symptom, but the hidden cost is failover RTO. On forced or automatic failover, the new primary must drain the entire redo queue before it accepts writes, so a queue that looks tolerable during steady state can turn a 30-second failover into a 30-minute one.

On a synchronous-commit replica, the send queue is even more dangerous. The primary waits for the secondary to harden log before acknowledging commit, so a secondary that hardens slowly converts directly into write latency on the primary. HADR_SYNC_COMMIT climbs, application commits stall, and what looked like a “secondary problem” becomes a primary outage.

Reading the queue DMV

The authoritative DMV is sys.dm_hadr_database_replica_states. It exposes per-database, per-replica queue sizes and rates:

-- Check AG send and redo queues per database/replica
SELECT
    ag.name AS ag_name,
    ar.replica_server_name,
    DB_NAME(drs.database_id) AS database_name,
    drs.synchronization_state_desc,
    drs.log_send_queue_size AS send_queue_kb,
    drs.log_send_rate AS send_rate_kb_sec,
    drs.redo_queue_size AS redo_queue_kb,
    drs.redo_rate AS redo_rate_kb_sec,
    CASE WHEN drs.redo_rate > 0
         THEN drs.redo_queue_size / drs.redo_rate
         ELSE NULL END AS estimated_redo_drain_seconds
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar ON drs.replica_id = ar.replica_id
JOIN sys.availability_groups ag ON ar.group_id = ag.group_id
ORDER BY drs.log_send_queue_size + drs.redo_queue_size DESC;

A common mistake is selecting drs.database_name. That column does not exist on sys.dm_hadr_database_replica_states. Always resolve the name with DB_NAME(drs.database_id) or a join to sys.databases. All queue and rate columns are bigint, expressed in KB, with rates in KB/sec.

The mental model is a two-stage pipeline:

flowchart LR
    P[Primary log generation] --> SQ[Send queue
log_send_queue_size]
    SQ -->|Network| HR[Secondary harden]
    HR --> RQ[Redo queue
redo_queue_size]
    RQ -->|Redo thread| RD[Redone on secondary]
    P -. Sync commit waits
if harden is slow .- HR
    RD -. Failover blocked
until drained .- RQ

Two failure shapes follow from this:

  • Growing send queue: the primary is producing log faster than it can be shipped or hardened on the secondary. On a sync replica, this blocks primary commits.
  • Growing redo queue: the secondary has the log but cannot apply it fast enough. The redo_queue_size / redo_rate ratio estimates how long failover would take.

Common causes

CauseWhat it looks likeFirst thing to check
Network throughput or latency between replicassend_queue grows, redo_queue near zero, send_rate below link capacityperfmon SQLServer:Availability Replica counters and OS-level RTT
Secondary log hardening slow (storage)send_queue grows on sync replica, HADR_SYNC_COMMIT waits rise on primarysys.dm_io_virtual_file_stats on secondary log file
Secondary redo thread saturatedredo_queue grows, send_queue near zero, redo_rate flatsecondary CPU, redo_rate, parallel redo thread count
Readable secondary queries blocking redoredo_queue grows during reporting windows, LCK_M_SCH_M waits on secondarysys.dm_exec_requests on secondary for Sch-S holders
Large log-generating workload on primaryboth queues grow together, log_flush_rate spikes on primaryprimary log generation rate, recent index rebuilds or bulk loads
Parallel redo thread exhaustion (pre-2022)redo_queue grows on instances with many AG databasesSQL Server version and parallel redo thread model
Flow control gates on primarysend_queue grows in step with flow control countersSQLServer:Database Replica flow control counters

Quick checks

Run these read-only queries against the primary (and the secondary where noted). None of them change state.

-- Replica health and connectivity
SELECT
    ag.name AS ag_name,
    ar.replica_server_name,
    ars.role_desc,
    ars.operational_state_desc,
    ars.connected_state_desc,
    ars.synchronization_health_desc
FROM sys.dm_hadr_availability_replica_states ars
JOIN sys.availability_replicas ar ON ars.replica_id = ar.replica_id
JOIN sys.availability_groups ag ON ar.group_id = ag.group_id;

-- HADR_SYNC_COMMIT impact on primary commits
SELECT wait_type, waiting_tasks_count, wait_time_ms,
       wait_time_ms / NULLIF(waiting_tasks_count, 0) AS avg_wait_ms
FROM sys.dm_os_wait_stats
WHERE wait_type = 'HADR_SYNC_COMMIT';

-- Secondary I/O stall on log file (run on secondary)
SELECT
    DB_NAME(vfs.database_id) AS database_name,
    mf.name AS file_name,
    vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes, 0) AS avg_log_write_ms,
    vfs.num_of_writes
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN sys.master_files mf
    ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
WHERE mf.type_desc = 'LOG'
ORDER BY vfs.io_stall_write_ms DESC;

-- Readable secondary blocking redo (run on secondary)
SELECT
    r.session_id,
    r.wait_type,
    r.wait_time / 1000 AS wait_seconds,
    r.blocking_session_id,
    t.text AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.wait_type IN ('LCK_M_SCH_M', 'LCK_M_SCH_S')
   OR r.blocking_session_id <> 0;

-- Primary log generation rate (sample twice, compute delta)
SELECT instance_name AS database_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Log Bytes Flushed/sec'
  AND object_name LIKE '%Databases%';

For network checks, measure round-trip at the SQL layer. A single TCP connection carries all AG traffic for a replica pair, so latency dominates throughput more than raw bandwidth.

# TCP settings relevant to AG throughput (Windows Server 2016+)
Get-NetTCPSetting | Select SettingName, CwndRestart
# CwndRestart = True resets the congestion window after idle, which can hurt long-distance replicas

How to diagnose it

  1. Identify which queue is growing. Run the queue DMV above twice, 30 to 60 seconds apart. Compute the delta of log_send_queue_size and redo_queue_size per database per replica. A queue that is static is not the problem.

  2. If only the send queue is growing, the primary is not getting log to the secondary fast enough. Compare send_rate to your network capacity. On a synchronous replica, also check HADR_SYNC_COMMIT waits on the primary, which directly measures the commit penalty.

  3. If only the redo queue is growing, the secondary has the log but cannot apply it. redo_rate tells you the current drain rate. Divide redo_queue_size by redo_rate to estimate the failover RTO this database would incur right now.

  4. If both queues are growing, do not assume the primary is the only cause. Either primary log generation has spiked, or the redo stage is so slow that backpressure has reached the primary. Look upstream first (index rebuilds, bulk loads, unbatched updates), then verify redo_rate has not collapsed.

  5. On the secondary, check for readable-secondary contention. Long-running read queries take schema stability (Sch-S) locks that conflict with redo when the primary performs DDL. The redo thread then blocks on LCK_M_SCH_M.

  6. Check the SQL Server version for parallel redo behavior. SQL Server 2016 and later support parallel redo, but pre-2022 builds are subject to a per-instance thread limit that can starve databases on busy instances.

Metrics and signals to monitor

SignalWhy it mattersWarning sign
log_send_queue_size (per database/replica)Measures log sitting on primary not yet shippedSustained nonzero on sync replica, or growing trend on async
redo_queue_size (per database/replica)Measures log on secondary not yet appliedSustained growth; drain time exceeds RTO target
redo_queue_size / redo_rateDirect failover RTO estimate in secondsExceeds your documented RTO SLA
log_send_rate, redo_rateThroughput of each pipeline stageRate flat while queue grows
synchronization_health_descAggregate AG healthPARTIALLY_HEALTHY or NOT_HEALTHY
HADR_SYNC_COMMIT waits (primary)Primary commit latency penalty from sync replicaRising wait time or average wait per task
SQLServer:Database Replica flow control countersPrimary throttling log send to protect itselfFlow Control Time (ms/sec) rising
Secondary log file write latencySecondary cannot harden log fast enoughAvg write latency above 5ms sustained
Secondary CPU and redo thread countRedo thread saturation or parallel redo limitsCPU near saturated with redo_rate capped
LCK_M_SCH_M waits on secondaryReadable queries blocking redoWaits appearing during reporting windows

Note: synchronization_health_desc can read HEALTHY while redo_queue_size grows on both sync and async replicas, because sync commit waits only for harden, not for redo. Treat the queue size and drain estimate as the source of truth for RTO, not the health flag.

Fixes

Send queue: network or secondary hardening

If send_rate is below your link capacity and the secondary is reachable, the bottleneck is usually secondary log hardening. Address the secondary log file I/O first: separate the log onto its own volume, confirm the storage tier meets your latency target, and check that antivirus or backup snapshots are not introducing stalls.

For replicas separated by long distances, check the TCP CwndRestart setting. Windows Server 2016 and later default to True, which resets the congestion window after idle periods and reduces throughput on high-latency links. Setting CwndRestart to False for the profile used by AG traffic can help. Because AG uses a single TCP connection per replica pair, latency affects throughput more than bandwidth. Test with a single-connection tool rather than parallel bandwidth tests.

If the send queue is growing because of a transient log burst on the primary (a large index rebuild, for example), the queue will drain when the burst ends. For synchronous replicas where HADR_SYNC_COMMIT is causing application-visible commit latency, a temporary switch to asynchronous commit is emergency relief only and accepts data loss risk on failover. Make that decision deliberately, not as a routine fix.

Redo queue: secondary cannot apply log

Start with the redo_rate. If it is near zero or far below what the hardware should deliver, look for contention:

  • Readable secondary queries holding Sch-S locks block redo on DDL. Kill or throttle the offending read workload during the catch-up window, or schedule heavy reporting around maintenance.
  • Pre-2022 builds with many AG databases can hit the parallel redo thread limit, leaving some databases on serial redo. SQL Server 2022 introduced a shared parallel redo thread pool that removes this ceiling. If you are stuck on an older build, balance databases across instances or reduce the database count on the saturated instance.
  • Trace Flag 3459 disables parallel redo and forces serial redo per database. It is a documented workaround when parallel redo itself is the problem, for example page split storms or heap table contention. Disabling parallel redo lowers the ceiling further, so use it only when parallel redo is the cause, not the cure.

For a redo queue that will not drain and is blocking a planned failover, some operators have reported that restarting the SQL Server service on the secondary lets crash recovery process the backlog faster than the live redo thread. This is a workaround, not a fix, and it takes the secondary out of service during restart. Validate it in a non-production replica before relying on it.

Both queues: primary log generation

When both queues are growing together, either the primary is producing more log than the pipeline can absorb or the redo stage is so slow that backpressure has reached the primary. Look upstream at the workload first. Common culprits are unbatched large updates, index rebuilds on production tables, or bulk loads in full recovery model. If primary log generation looks normal, suspect redo backpressure and apply the redo_queue fixes above. Otherwise, address the workload directly: batch large updates, schedule index maintenance, or move bulk loads to a non-AG path.

Prevention

  • Track redo_queue_size / redo_rate as a regular metric, not just during incidents. Alert when the estimated drain time approaches your failover RTO SLA.
  • For synchronous replicas, alert on HADR_SYNC_COMMIT average wait time per task. Sustained growth is the early signal that the secondary is becoming a primary bottleneck.
  • Validate failover RTO with planned failover drills. A HEALTHY synchronization_health_desc hides the redo queue size on both sync and async replicas, because sync commit does not wait for redo.
  • If you run readable secondaries, document the reporting workload windows and watch for LCK_M_SCH_M waits during those windows.
  • On pre-2022 builds with many AG databases, plan the upgrade. The parallel redo thread model in 2022 eliminates a class of redo starvation that is otherwise very hard to tune around.

How Netdata helps

  • Per-second collection of sys.dm_hadr_database_replica_states makes send and redo queue growth visible as a trend, not a point-in-time snapshot, which is essential for separating steady-state backlog from a divergent queue.
  • Correlate redo_queue_size against secondary CPU, secondary log file write latency, and redo_rate in the same view to localize whether redo is bound by CPU, storage, or lock contention.
  • On synchronous replicas, trend HADR_SYNC_COMMIT wait time alongside primary commit latency and application-facing query duration to make the primary impact of a slow secondary obvious.
  • Estimated RTO derived from redo_queue_size / redo_rate can be alerted on directly, so you catch the gap between “AG is healthy” and “failover will exceed SLA” before an actual failover forces the discovery.
  • Anomaly detection on send and redo queue sizes flags divergence from the per-database baseline, which matters because absolute queue size is workload-dependent and fixed thresholds are noisy.

Netdata’s Microsoft SQL Server monitoring brings these signals together with per-second metrics and anomaly detection.