DBA Scripts: Check Always On Availability Group Latency

How Far Behind Is Each Database, Really

A readable secondary is serving stale data, commits on the primary have slowed, or a failover is about to depend on a replica you have not checked. Run this on the primary: it returns every availability database on every replica with its send queue, redo queue and rates, so you can see which database is behind and by how much. Availability Group Replica State covers the replica-level question, connected or not.

Why Availability Group Latency Matters

• log_send_queue_size growing faster than log_send_rate can drain it means the secondary is falling further behind over time, not just momentarily busy
• redo_queue_size matters specifically for readable secondaries and failover time, a large redo queue means a longer wait before a failed-over secondary is actually usable
• On a synchronous-commit replica every commit on the primary waits for the secondary to harden the log, so a slow secondary shows up first as slow commits and HADR_SYNC_COMMIT waits, while the database still reads SYNCHRONIZED and HEALTHY
• On an asynchronous-commit replica, synchronization_health_desc reads HEALTHY whenever the database is SYNCHRONIZING, however large the queues grow. The queue sizes are the only warning

When to Run This Script

• Routine SQL Server health checks on any AG-protected instance
• When application teams report slow commits on a synchronous-commit AG primary
• Before and after a planned failover, to confirm the target replica is genuinely caught up first
• After a large batch load or index maintenance operation, to see how much log traffic it generated for replicas to absorb

The Script

Run the following script against your SQL Server instance.

/*
Script Name : Get-AvailabilityGroupLatency
Category    : high-availability
Purpose     : Display AG replica synchronization timing, queue health, and replication rates.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-check-always-on-availability-group-latency/)
Requires    : VIEW SERVER STATE
HealthCheck : Yes
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

IF SERVERPROPERTY('IsHadrEnabled') = 0
    OR NOT EXISTS (SELECT 1 FROM sys.availability_groups)
BEGIN
    SELECT 'Always On Availability Groups is not enabled or no groups are configured on this instance.' AS status;
END
ELSE
BEGIN

SELECT
    ag.name                          AS ag_name,
    ar.replica_server_name,
    ars.role_desc,
    DB_NAME(drs.database_id)         AS database_name,
    drs.synchronization_state_desc,
    drs.synchronization_health_desc,
    drs.last_hardened_time,
    drs.last_redone_time,
    drs.log_send_queue_size,
    drs.log_send_rate,
    drs.redo_queue_size,
    drs.redo_rate
FROM sys.dm_hadr_database_replica_states    AS drs
INNER JOIN sys.availability_replicas        AS ar  ON ar.replica_id  = drs.replica_id
INNER JOIN sys.availability_groups          AS ag  ON ag.group_id    = ar.group_id
INNER JOIN sys.dm_hadr_availability_replica_states AS ars ON ars.replica_id = ar.replica_id
ORDER BY ag.name, database_name, ar.replica_server_name;

END

Run it on the primary. There, sys.dm_hadr_database_replica_states returns a row for each primary database and one for each of its secondary databases, so an AG with several databases gives one row per database per replica and a single lagging database stands out. On a secondary it returns only that replica’s own databases. Where Always On is not enabled, or no availability group exists, the guard at the top returns one status row instead of an error.

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Check per-database AG sync queue size and rates:
.\run.ps1 Get-AvailabilityGroupLatency

# To run against a remote sql server:
.\run.ps1 Get-AvailabilityGroupLatency -ServerInstance SQLSERVER01

This script lives in the repo at:

• sql/high-availability/always-on/Get-AvailabilityGroupLatency.sql
• powershell/wrappers/high-availability/Get-AvailabilityGroupLatency.ps1

Example Output

With no availability group to report, the whole result is one status row:

If you get this row on a server that should host a replica, check SERVERPROPERTY('IsHadrEnabled'). 0 means the Always On feature is off there. 1 means the feature is on but the login that ran the script can see no availability group.

Understanding the Results

Best Practices

• Watch trend, not just a single snapshot, a queue size that’s stable is very different from one that’s climbing every time you check
• For synchronous-commit AGs, treat any sustained non-zero log_send_queue_size as worth investigating, that mode exists specifically to keep this near zero
• Check redo_queue_size specifically before relying on a readable secondary for reporting, or before a planned failover to that replica
• Pair with Availability Group Replica State, connection health and queue latency are two different failure modes that can occur independently

MS Docs: sys.dm_hadr_database_replica_states · sys.dm_hadr_availability_replica_states · sys.availability_groups · Monitor performance for availability groups

Related Scripts

You may also find these scripts useful:

• High Availability (hub)
• Availability Group Replica State
• Log Reuse Waits
• Replication Status
• Transaction Log Size and Usage
• AG Failover Readiness and Readable Secondary Usage
• Understanding SQL Server AG Latency

Frequently Asked Questions

Summary

Replica connection state answers whether an Availability Group is broadly healthy. This script answers how far behind each database is, in KB of log waiting to be sent and waiting to be redone. Run it a few times a minute apart: a queue that climbs between runs is falling behind, one that shrinks is catching up.

Run this alongside Availability Group Replica State as routine health checks, and specifically before trusting a secondary for reporting or a planned failover.