SQL Server Availability Group secondary falling behind: log send queue vs redo queue, blocked redo and parallel redo bottlenecks

#Symptoms

• The AG dashboard shows a large redo queue or log send queue on a secondary; estimated recovery time grows.
• Reports on the readable secondary return data that is minutes or hours old.
• A planned failover takes much longer than expected, because the new primary must redo the backlog first.
• On the primary, commits slow down with HADR_SYNC_COMMIT waits (synchronous replicas).

#Step 1: which queue?

Run on the primary:

SELECT ag.name AS ag, ar.replica_server_name AS replica, DB_NAME(drs.database_id) AS db,
       drs.synchronization_state_desc AS sync_state,
       drs.log_send_queue_size AS send_queue_kb, drs.log_send_rate AS send_rate_kb_s,
       drs.redo_queue_size AS redo_queue_kb, drs.redo_rate AS redo_rate_kb_s,
       drs.secondary_lag_seconds, drs.last_commit_time
FROM   sys.dm_hadr_database_replica_states drs
JOIN   sys.availability_replicas ar ON ar.replica_id = drs.replica_id
JOIN   sys.availability_groups ag ON ag.group_id = drs.group_id
WHERE  drs.is_local = 0
ORDER  BY drs.redo_queue_size DESC;

redo_queue_kb / redo_rate_kb_s ≈ seconds to catch up (if the rate holds).

#Step 2: redo queue - is redo blocked?

On a readable secondary, redo must sometimes take schema locks (for example when the primary did DDL, an index rebuild, or ghost cleanup). Long-running read queries holding schema stability locks block redo.

Find it on the secondary:

SELECT r.session_id, r.command, r.wait_type, r.wait_time, r.blocking_session_id, DB_NAME(r.database_id) AS db
FROM   sys.dm_exec_requests r
WHERE  r.command IN ('DB STARTUP', 'PARALLEL REDO TASK', 'PARALLEL REDO HELP TASK')
   OR  r.session_id IN (SELECT blocking_session_id FROM sys.dm_exec_requests WHERE command = 'DB STARTUP');

Redo threads appear with command DB STARTUP (and parallel redo task commands). A non-zero blocking_session_id on a redo thread points at the query blocking it.

The extended event sqlserver.lock_redo_blocked records each time redo is blocked:

CREATE EVENT SESSION redo_blocked ON SERVER
ADD EVENT sqlserver.lock_redo_blocked
ADD TARGET package0.event_file (SET filename = N'redo_blocked.xel', max_file_size = 50)
WITH (STARTUP_STATE = ON);
ALTER EVENT SESSION redo_blocked ON SERVER STATE = START;

Fixes:

• Kill or tune the long report query; schedule heavy reports away from index maintenance windows.
• Avoid large online index rebuilds on the primary during reporting hours; use resumable rebuilds in smaller chunks.
• Use snapshot-friendly queries; avoid long transactions on the secondary.

#Step 3: redo queue - redo is running but slow

Waits on the secondary:

SELECT TOP (15) wait_type, waiting_tasks_count, wait_time_ms
FROM   sys.dm_os_wait_stats
WHERE  wait_type LIKE 'PARALLEL_REDO%' OR wait_type LIKE 'REDO%' OR wait_type LIKE 'PAGEIOLATCH%' OR wait_type LIKE 'HADR%'
ORDER  BY wait_time_ms DESC;

Parallel redo (default since SQL Server 2016) uses several threads per database, from a shared pool. With many databases in AGs, the pool is shared and some databases fall back to serial redo. SQL Server 2022 improved parallel redo thread pooling and batching. Trace flags exist to switch to serial redo (TF 3459) for specific contention problems; use them only on Microsoft guidance after testing.

#Step 4: log send queue

• Check network throughput and latency between replicas (especially cross-region async replicas).
• Check log_send_rate against the primary's log generation rate (Log Bytes Flushed/sec counter).
• Flow control: when a secondary can't keep up, the primary throttles sending; this shows as HADR_FLOW_CONTROL related waits on the primary.

-- Primary: log generation rate per database (sample twice and diff)
SELECT instance_name AS db, cntr_value
FROM   sys.dm_os_performance_counters
WHERE  counter_name = 'Log Bytes Flushed/sec' AND object_name LIKE '%:Databases%';

For WAN replicas, AG log stream compression (on by default for asynchronous replicas) helps; reduce log volume at the source where possible.

#Step 5: synchronous commit impact on the primary

With synchronous commit, a slow secondary slows every commit on the primary (HADR_SYNC_COMMIT). Options:

• Fix the secondary's I/O or network.
• Switch a remote replica to asynchronous commit (accepting data loss risk on failover to it):

ALTER AVAILABILITY GROUP [ag_sales]
MODIFY REPLICA ON N'SQLDR01' WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT);

Warning

Changing availability mode changes your RPO for failovers to that replica. Agree it with the business owner and update the DR runbook.

#Monitoring

• Alert on redo_queue_size and secondary_lag_seconds per database, not just synchronization state.
• Collect lock_redo_blocked events on readable secondaries.
• Track log generation rate during maintenance windows; it predicts lag spikes.

#Prevent it next time

• Size secondaries (CPU, memory, storage) the same as the primary.
• Schedule index maintenance and large batch operations away from reporting peaks on readable secondaries.
• Use resumable index operations and smaller batches for big changes.
• Review AG health weekly: send queue, redo queue and recovery time estimates.