Sqlhadr Review

SQL Server Always On AG Health Review Skill

Purpose

Analyze output from the sys.dm_hadr_* DMV family to assess the health of one or more Always On Availability Groups. Applies 27 checks (H1–H28, with H21 retired and merged into sqlag-review F15 — see Category 4) across six categories:

• H1–H6 — Replica connectivity and role: detect disconnected replicas, resolving state,

unhealthy synchronization health, replicas not synchronizing, last-connect errors, and failover mode mismatches

• H7–H11 — Data loss and recovery time: flag estimated data loss, excessive recovery time,

secondary lag, redo queue buildup, and log send queue buildup

• H12–H16 — Throughput and performance: detect stalled redo rate, stalled log send rate,

rate mismatch causing queue accumulation, multiple databases lagging on the same replica, and commit latency signals on sync-commit replicas

• H17–H22 — Configuration: async replica in unexpected position, no automatic failover

replica, single-replica AG, missing listener, and automatic seeding in progress (H21 is retired — read-only routing absence is covered by sqlag-review F15)

• H23–H27 — Modern AG features: Contained AG DML misrouting, Cloud Witness inaccessible, Parallel Redo saturation, Read-Scale secondary missing RCSI, AG without database-level health detection (SQL 2012–2022+)
• H28 — Seeding and initialization integrity: database stuck in INITIALIZING synchronization state, particularly after a failover

Input

Accept any of:

• File path — path to a saved text/CSV file containing the DMV query output
• Inline paste — DMV result grid pasted directly into chat (tab- or pipe-delimited)
• Natural language description — description of AG symptoms ("secondary is 90 seconds

behind", "replica shows NOT_HEALTHY")

Capture Query

Run the following on the primary replica to collect the required columns:

SELECT
    ag.name                              AS ag_name,
    ar.replica_server_name,
    ar.availability_mode_desc,
    ar.failover_mode_desc,
    ars.role_desc,
    ars.connected_state_desc,
    ars.synchronization_health_desc,
    ars.last_connect_error_number,
    ars.last_connect_error_description,
    drs.database_name,
    drs.synchronization_state_desc,
    drs.synchronization_health_desc      AS db_sync_health,
    drs.log_send_queue_size,
    drs.log_send_rate,
    drs.redo_queue_size,
    drs.redo_rate,
    drs.secondary_lag_seconds,          /* SQL Server 2016+ only; NULL on 2014 and earlier */
    drs.estimated_data_loss_seconds,
    drs.estimated_recovery_time_seconds
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar
    ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars
    ON ar.replica_id = ars.replica_id
JOIN sys.dm_hadr_database_replica_states drs
    ON ar.replica_id = drs.replica_id
ORDER BY ar.replica_server_name, drs.database_name;

Also capture listener configuration for H20 (and sqlag-review F15, which covers read-only routing — H21 is retired):

SELECT ag.name AS ag_name, agl.dns_name, agl.port,
       aglip.ip_address, aglip.ip_subnet_mask,
       r.replica_server_name, r.read_only_routing_url
FROM sys.availability_group_listeners agl
JOIN sys.availability_groups ag ON agl.group_id = ag.group_id
JOIN sys.availability_group_listener_ip_addresses aglip
    ON agl.listener_id = aglip.listener_id
JOIN sys.availability_replicas r ON ag.group_id = r.group_id;

Column Reference

| Column | Source DMV | Notes | |--------|-----------|-------| | connected_state_desc | dm_hadr_availability_replica_states | CONNECTED or DISCONNECTED | | role_desc | dm_hadr_availability_replica_states | PRIMARY, SECONDARY, RESOLVING | | synchronization_health_desc (replica) | dm_hadr_availability_replica_states | NOTHEALTHY, PARTIALLYHEALTHY, HEALTHY | | last_connect_error_number | dm_hadr_availability_replica_states | 0 = no error | | last_connect_error_description | dm_hadr_availability_replica_states | Error text when non-zero | | availability_mode_desc | sys.availability_replicas | SYNCHRONOUSCOMMIT or ASYNCHRONOUSCOMMIT | | failover_mode_desc | sys.availability_replicas | AUTOMATIC or MANUAL | | synchronization_state_desc | dm_hadr_database_replica_states | NOT SYNCHRONIZING, SYNCHRONIZING, SYNCHRONIZED | | db_sync_health | dm_hadr_database_replica_states | NOTHEALTHY, PARTIALLYHEALTHY, HEALTHY | | log_send_queue_size | dm_hadr_database_replica_states | KB of log not yet sent to secondary | | log_send_rate | dm_hadr_database_replica_states | KB/s sent to secondary (0 = stalled) | | redo_queue_size | dm_hadr_database_replica_states | KB of log received but not yet redone | | redo_rate | dm_hadr_database_replica_states | KB/s being redone on secondary (0 = stalled) | | secondary_lag_seconds | dm_hadr_database_replica_states | Seconds secondary is behind primary | | estimated_data_loss_seconds | dm_hadr_database_replica_states | Potential data loss if primary fails now | | estimated_recovery_time_seconds | dm_hadr_database_replica_states | Seconds to redo queued log after failover |

---

Thresholds Reference

| Threshold | Value | Used by | |-----------|-------|---------| | Estimated data loss | >30 sec → Critical; >5 sec → Warning | H7 | | Estimated recovery time | >300 sec → Warning | H8 | | Secondary lag | >60 sec → Critical; >10 sec → Warning | H9 | | Redo queue size | >500 MB → Critical; >100 MB → Warning | H10 | | Log send queue size | >500 MB → Warning | H11 | | Multiple databases lagging | ≥3 databases with secondarylagseconds >10 sec on same replica → Critical | H15 |

---

Category 1 — Replica Connectivity and Role (H1–H6)

Evaluate these first. A disconnected or resolving replica supersedes all other findings.

H1 — Replica Disconnected

• Trigger: connected_state_desc = DISCONNECTED for any replica row
• Severity: Critical
• Fix: Check network connectivity between the primary and the disconnected node. Review

last_connect_error_description for the specific failure. Inspect CLUSTER.LOG on the Windows Server Failover Cluster node for eviction or network partition events. Confirm the SQL Server service is running on the target node.

H2 — Replica in Resolving State

• Trigger: role_desc = RESOLVING for any replica row
• Severity: Critical
• Fix: A replica in RESOLVING state has lost quorum contact or its role cannot be determined.

Check WSFC quorum health in Failover Cluster Manager. If this is a planned failover in progress, wait for it to complete. If unplanned, investigate CLUSTER.LOG for quorum loss.

H3 — Synchronization Unhealthy at Replica Level

• Trigger: synchronization_health_desc = NOT_HEALTHY on a replica row
• Severity: Critical
• Fix: At least one database on this replica is not synchronizing. Drill into

db_sync_health per database to identify which database is unhealthy (H4 will co-fire). Check the SQL Server ERRORLOG on the secondary for hadrworkqueue or transport errors.

H4 — Replica Not Synchronizing (Sync-Commit)

• Trigger: synchronization_state_desc = NOT SYNCHRONIZING AND `availabilitymodedesc

= SYNCHRONOUS_COMMIT`

• Severity: Critical
• Fix: Clarify the behaviour: while a sync-commit secondary is connected but lagging

(SYNCHRONIZING), commits on the primary incur added latency waiting for the secondary to harden the log. Once the secondary disconnects or its session times out and it moves to NOT SYNCHRONIZING/NOT SYNCHRONIZED, the primary stops waiting and commits proceed — per MS Learn, "the primary stops waiting for confirmation… so a failed synchronous-commit secondary doesn't prevent log hardening on the primary." The real exposure is loss of synchronous HA: a primary failure now risks data loss until sync is restored. Resume the secondary if otherwise healthy: ALTER DATABASE [db] SET HADR RESUME. Check for a full transaction log on the secondary — a full log halts redo and breaks synchronization.

H5 — Last Connect Error Present

• Trigger: last_connect_error_number != 0
• Severity: Warning
• Fix: A past connection failure was recorded. The replica may have recovered, but the

error reveals prior instability. Review last_connect_error_description for the error text. Common causes: endpoint certificate expiry, firewall change, or network blip. Rotate certificates if the error mentions authentication or certificate issues.

H6 — Manual Failover Mode on Sync-Commit Replica

• Trigger: failover_mode_desc = MANUAL AND availability_mode_desc = SYNCHRONOUS_COMMIT
• Severity: Warning
• Fix: A synchronous-commit replica configured for manual failover only will not

automatically protect against primary failure. If automatic protection is intended, change to AUTOMATIC failover mode: ALTER AVAILABILITY GROUP [ag] MODIFY REPLICA ON N'server' WITH (FAILOVER_MODE = AUTOMATIC). Verify WSFC quorum can support automatic failover before making this change.

---

Category 2 — Data Loss and Recovery Time (H7–H11)

These checks quantify the risk of data loss and the time to recover if the primary fails.

H7 — Estimated Data Loss

• Trigger: estimated_data_loss_seconds exceeds the data loss threshold (see Thresholds

Reference)

• Severity: Critical if >30 sec; Warning if >5 sec
• Fix: The log has not been hardened on the secondary within the threshold window. For

sync-commit replicas, this indicates the synchronization is stalled (see H4). For async replicas, consider increasing log send rate, improving network bandwidth, or accepting the RPO by switching a critical database to sync-commit. If the value is consistently high, evaluate whether the secondary has sufficient I/O to keep up with redo.

H8 — Estimated Recovery Time

• Trigger: estimated_recovery_time_seconds exceeds the recovery time threshold (see

Thresholds Reference)

• Severity: Warning
• Fix: After a failover, it will take longer than the threshold to redo the queued log on

the secondary before it opens for reads or promotes to primary. Reduce redo queue size (H10) to reduce recovery time. Check secondary disk I/O — redo is sequential log apply and is bounded by disk write throughput. Evaluate whether this RTO is acceptable for the SLA.

H9 — Secondary Lag

• Trigger: secondary_lag_seconds exceeds the lag threshold (see Thresholds Reference). Version note: secondary_lag_seconds was added in SQL Server 2016; this column does not exist in SQL Server 2014 and earlier — skip H9 if the instance is pre-2016
• Severity: Critical if >60 sec; Warning if >10 sec
• Fix: The secondary is behind the primary. For async replicas, check log_send_rate

(H13) — if zero, log is not being sent. For sync replicas, lag indicates the primary is waiting on acknowledgement. Check network latency between primary and secondary. On the secondary, check for I/O bottlenecks limiting redo throughput (redo_rate, H12). If secondarylagseconds equals estimateddataloss_seconds, the lag is entirely in the send queue; if recovery time is also high, redo is behind as well.

H10 — Redo Queue Buildup

• Trigger: redo_queue_size exceeds the redo queue threshold (see Thresholds Reference)
• Severity: Critical if >500 MB; Warning if >100 MB
• Fix: Log records are arriving on the secondary faster than they are being redone. The

secondary's redo thread cannot keep up. Check secondary disk write latency — redo is bottlenecked on sequential log writes to the data files. Consider increasing secondary storage throughput (SSD, faster controller). Check for long-running transactions on the secondary blocking redo (readable secondary scenario). Verify redo_rate > 0 (see H12).

H11 — Log Send Queue Buildup

• Trigger: log_send_queue_size exceeds the send queue threshold (see Thresholds

Reference)

• Severity: Warning
• Fix: Log generated on the primary has not been sent to the secondary. Check network

bandwidth between primary and secondary. High log_send_queue_size with log_send_rate = 0 (see H13) indicates a stalled transport — check endpoint connectivity. High log_send_queue_size with nonzero log_send_rate indicates network saturation or burst log generation outpacing the link.

---

Category 3 — Throughput and Performance (H12–H16)

These checks detect stalled or mismatched throughput that will cause queues to grow.

H12 — Zero Redo Rate on Synchronizing Database

• Trigger: redo_rate = 0 AND synchronization_state_desc = SYNCHRONIZING AND

redo_queue_size > 0

• Severity: Warning
• Fix: The redo thread has stalled despite queued log. Common causes: (1) long-running

read query on a readable secondary holding a lock that blocks redo; (2) the secondary database is in a transitional state — check ERRORLOG; (3) redo thread has encountered an error — check dm_hadr_database_replica_states.last_redone_lsn for progress. Restarting HADR on the secondary (ALTER DATABASE [db] SET HADR SUSPEND / RESUME) can clear transient stalls.

H13 — Zero Log Send Rate with Non-Empty Send Queue

• Trigger: log_send_rate = 0 AND log_send_queue_size > 0
• Severity: Warning
• Fix: Log is queued but not being sent. The HADR transport thread has stalled. Check

endpoint health: SELECT * FROM sys.dm_hadr_availability_replica_states WHERE connected_state_desc = 'DISCONNECTED'. Verify the database mirroring endpoint is running: SELECT state_desc FROM sys.database_mirroring_endpoints. Restart the endpoint if necessary: ALTER ENDPOINT [Hadr_endpoint] STATE = STOPPED; ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED.

H14 — Redo Rate / Send Rate Mismatch

• Trigger: log_send_rate > 0 AND redo_rate > 0 AND redo_queue_size is growing

(redorate significantly less than logsend_rate, such that the queue accumulates)

• Severity: Warning
• Fix: Log is being sent faster than the secondary can redo it, causing redo queue

growth. The bottleneck is secondary redo throughput, not the network. Investigate secondary disk I/O latency. Check whether readable secondary workloads (reporting queries) are competing with redo for I/O. Consider dedicated storage for secondary data files.

H15 — Multiple Databases Lagging on Same Replica

• Trigger: ≥3 databases on the same replica have secondary_lag_seconds exceeding the

multiple-database lag threshold (see Thresholds Reference)

• Severity: Critical
• Fix: When multiple databases lag simultaneously, the root cause is at the replica level,

not per-database. Check overall secondary node health: CPU, memory, and disk I/O. A saturated secondary node falls behind across all databases at once. Also check CLUSTER.LOG for node-level resource pressure. Investigate whether a single database with large transactions is monopolizing redo threads.

H16 — Commit Latency Signal on Sync-Commit Replica

• Trigger: availability_mode_desc = SYNCHRONOUS_COMMIT AND

synchronization_state_desc = SYNCHRONIZING (database not yet SYNCHRONIZED, indicating the sync is in progress but not complete, potentially stalling primary commits)

• Severity: Warning
• Fix: Primary commits wait for the synchronous secondary to harden the log before

acknowledging. While SYNCHRONIZING is normal during catchup, a sync-commit secondary that remains SYNCHRONIZING for an extended period adds latency to every primary transaction. Check estimated_data_loss_seconds and secondary_lag_seconds to quantify the stall. If the secondary is persistently SYNCHRONIZING, investigate redo and send queue (H10, H11).

---

Category 4 — Configuration (H17–H22)

These checks surface AG topology gaps that may not cause immediate

…

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: vanterx
• Source: vanterx/mssql-performance-skills
• License: MIT

Install and usage instructions live in the source repository linked above.