HADR_LOGPROGRESS_SYNC Wait Type Explained

This wait type manifests when the Always On log capture thread and log send threads contend for the internal log progress synchronization object. SQL Server maintains a shared data structure tracking the log sequence number (LSN) progress for each replica in the availability group. When primary replica transactions generate log records faster than secondaries can acknowledge receipt, multiple threads simultaneously attempt to update this progress tracking structure.

The lock manager protects this critical metadata using lightweight synchronization primitives. Under heavy transactional load, the log capture thread frequently updates the last-sent LSN while log send threads update acknowledgment LSNs from secondaries. This creates a bottleneck where threads queue waiting for exclusive access to modify the progress tracking structure.

SQL Server 2016 introduced optimizations to reduce contention by implementing finer-grained locking around LSN tracking. SQL Server 2019 further improved this by separating the progress tracking for different replicas into distinct synchronization contexts. However, configurations with many synchronous replicas or slow network links still experience significant waits.

The scheduler becomes involved when these waits exceed the quantum threshold, potentially causing SQLOS to yield threads and reschedule them. Buffer pool involvement is minimal since this wait occurs in the log management subsystem before data pages are affected.

-- Check current HADR_LOGPROGRESS_SYNC waits and timing
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_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_LOGPROGRESS_SYNC';

-- Identify AG replica lag and synchronization health
SELECT 
    ar.replica_server_name,
    db.database_name,
    drs.synchronization_state_desc,
    drs.log_send_queue_size,
    drs.log_send_rate,
    drs.redo_queue_size,
    drs.redo_rate,
    drs.last_sent_lsn,
    drs.last_received_lsn,
    drs.last_hardened_lsn
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar ON drs.replica_id = ar.replica_id
JOIN sys.databases db ON drs.database_id = db.database_id;

-- Monitor log generation rate and AG performance counters
SELECT 
    instance_name,
    counter_name,
    cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Availability Replica%'
AND counter_name IN ('Bytes Sent to Replica/sec', 'Bytes Sent to Transport/sec', 
                     'Sends to Replica/sec', 'Sends to Transport/sec')
ORDER BY instance_name, counter_name;

-- Check for blocking in log send threads
SELECT 
    s.session_id,
    s.login_name,
    r.command,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    t.text
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.program_name LIKE 'AlwaysOn%'
AND r.wait_type = 'HADR_LOGPROGRESS_SYNC';

-- Analyze transaction log activity by database
SELECT 
    db.name,
    ls.cntr_value AS log_bytes_flushed_sec
FROM sys.databases db
JOIN sys.dm_os_performance_counters ls ON db.name = ls.instance_name
WHERE ls.counter_name = 'Log Bytes Flushed/sec'
AND ls.cntr_value > 0
ORDER BY ls.cntr_value DESC;

Optimize transaction log VLF structure

-- Check VLF count and optimize if fragmented
-- Run on primary replica only
DECLARE @DBName NVARCHAR(128) = 'YourAGDatabase'
DECLARE @VLFCount INT

SELECT @VLFCount = COUNT(*)
FROM sys.dm_db_log_info(DB_ID(@DBName))

IF @VLFCount > 50
BEGIN
    PRINT 'High VLF count detected: ' + CAST(@VLFCount AS VARCHAR(10))
    PRINT 'Consider shrinking and re-growing log file in appropriate increments'
    
    -- Example for 10GB database (adjust size accordingly)
    -- Test in development first
    /*
    BACKUP LOG [YourAGDatabase] TO DISK = 'NUL:'
    DBCC SHRINKFILE('YourAGDatabase_Log', 1)
    ALTER DATABASE [YourAGDatabase] MODIFY FILE (NAME = 'YourAGDatabase_Log', SIZE = 10240MB, FILEGROWTH = 1024MB)
    */
END

Adjust synchronous replica configuration

-- Convert synchronous replicas to asynchronous for better performance
-- Impact: Reduces transaction latency but increases potential data loss
-- Test failover procedures after making this change

USE master
GO

-- Check current replica modes
SELECT 
    ar.replica_server_name,
    ar.availability_mode_desc,
    ar.failover_mode_desc
FROM sys.availability_replicas ar
JOIN sys.availability_groups ag ON ar.group_id = ag.group_id
WHERE ag.name = 'YourAGName'

-- Convert to asynchronous commit (adjust replica name)
/*
ALTER AVAILABILITY GROUP [YourAGName]
MODIFY REPLICA ON 'SecondaryServerName'
WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT)
*/

Increase log send buffer size

-- Modify AG to use larger send buffers (SQL Server 2016+)
-- Impact: Reduces frequency of progress updates, may increase memory usage
-- Requires AG offline briefly during modification

/*
ALTER AVAILABILITY GROUP [YourAGName]
MODIFY REPLICA ON 'PrimaryServerName'
WITH (SEEDING_MODE = MANUAL)  -- Forces buffer optimization
*/

-- Monitor improvement with extended events
CREATE EVENT SESSION [HADR_LogProgress_Monitor] ON SERVER 
ADD EVENT sqlserver.hadr_log_block_compression,
ADD EVENT sqlserver.hadr_log_block_send
ADD TARGET package0.event_file(SET filename=N'C:\temp\hadr_log_progress.xel')
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, 
      MAX_DISPATCH_LATENCY=30 SECONDS, STARTUP_STATE=OFF)

Configure proper transaction log sizing

-- Set appropriate initial size and growth for transaction logs
-- Impact: Prevents auto-growth events that can cause log progress waits

DECLARE @DatabaseName NVARCHAR(128) = 'YourAGDatabase'
DECLARE @LogicalName NVARCHAR(128)

SELECT @LogicalName = name 
FROM sys.master_files 
WHERE database_id = DB_ID(@DatabaseName) 
AND type = 1  -- Log file

-- Size based on peak transaction volume (adjust accordingly)
DECLARE @SQL NVARCHAR(MAX) = 
'ALTER DATABASE [' + @DatabaseName + '] 
 MODIFY FILE (
    NAME = ''' + @LogicalName + ''', 
    SIZE = 20480MB,      -- 20GB initial size
    FILEGROWTH = 2048MB  -- 2GB growth increments
 )'

PRINT @SQL
-- Execute after validating in development
-- EXEC sp_executesql @SQL

Configure transaction log files with appropriate initial sizes based on peak transaction volume, typically 20-30% of database size for OLTP workloads. Set growth increments to large fixed values (1-2GB) rather than percentage-based growth to minimize VLF fragmentation. Monitor VLF counts monthly and rebuild logs when counts exceed 50-100 VLFs.

Implement network bandwidth monitoring between AG replicas. Ensure minimum 1Gbps dedicated bandwidth for production AGs with sustained throughput above 100MB/sec. Configure synchronous replicas only for databases requiring zero data loss, as each synchronous replica adds cumulative latency to transaction commits.

Establish baseline performance counters for "Bytes Sent to Replica/sec" and "Log Send Queue Size" per replica. Alert when log send queues exceed 100MB or when send rates drop below expected thresholds. Deploy AG replicas across similar hardware specifications to prevent performance bottlenecks from slower secondary replicas.

Consider partitioning high-volume transactional databases to distribute log generation across multiple AG groups. This reduces contention on individual log progress synchronization objects. For SQL Server 2019+, enable accelerated database recovery to minimize log dependencies and improve AG performance during high transaction periods.