hadr-health-review

---
name: hadr-health-review
description: Analyzes sys.dm_hadr_* DMV output to assess Always On Availability Group replica health, synchronization state, secondary lag, redo and log send queue sizes, and configuration gaps. Use this skill when an availability group is behaving unexpectedly, a secondary replica is lagging, data loss is a concern, or you need a SQL-side snapshot of AG health to complement CLUSTER.LOG and ERRORLOG diagnostics. Applies 22 checks (H1–H22) covering replica connectivity, data loss risk, recovery time, throughput, and configuration.
triggers:
  - /hadr-health-review
  - /hadr-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 22 checks (H1–H22) across four 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, read-only routing not configured, and
  automatic seeding in progress

## 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:

```sql
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,
    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–H21:

```sql
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` | NOT_HEALTHY, PARTIALLY_HEALTHY, 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` | SYNCHRONOUS_COMMIT or ASYNCHRONOUS_COMMIT |
| `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` | NOT_HEALTHY, PARTIALLY_HEALTHY, 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 secondary_lag_seconds >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 hadr_work_queue or transport errors.
### H4 — Replica Not Synchronizing (Sync-Commit)
- **Trigger:** `synchronization_state_desc = NOT SYNCHRONIZING` AND `availability_mode_desc
  = SYNCHRONOUS_COMMIT`
- **Severity:** Critical
- **Fix:** A synchronous-commit secondary that is not synchronizing blocks commits on the
  primary (the primary waits for acknowledgement). Restart HADR transport if the secondary
  is otherwise healthy: `ALTER DATABASE [db] SET HADR RESUME`. Check for 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)
- **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
  secondary_lag_seconds equals estimated_data_loss_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
  (redo_rate significantly less than log_send_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 problems but increase risk.
### H17 — Async Replica in Sync-Expected Position
- **Trigger:** `availability_mode_desc = ASYNCHRONOUS_COMMIT` on a replica that is the
  only secondary in the AG, or is designated as the DR target in a two-replica topology
- **Severity:** Info
- **Fix:** An async-commit secondary provides no data-loss protection for synchronous RPO
  requirements. If the topology intends zero data loss, change the replica to
  SYNCHRONOUS_COMMIT: `ALTER AVAILABILITY GROUP [ag] MODIFY REPLICA ON N'server' WITH
  (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT)`. Verify the network and I/O can sustain the
  additional commit latency before switching.
### H18 — No Automatic Failover Replica
- **Trigger:** No replica in the AG has `failover_mode_desc = AUTOMATIC`
- **Severity:** Warning
- **Fix:** Without an automatic failover replica, a primary failure requires manual
  intervention, increasing recovery time. Configure at least one synchronous-commit secondary
  for automatic failover: `ALTER AVAILABILITY GROUP [ag] MODIFY REPLICA ON N'server' WITH
  (FAILOVER_MODE = AUTOMATIC)`. Confirm WSFC quorum supports automatic failover before
  enabling it.
### H19 — Single Replica AG
- **Trigger:** Only one replica row exists for the availability group (no secondaries)
- **Severity:** Info
- **Fix:** A single-replica AG provides readable secondary benefits (for local replicas) but
  no high availability protection. Add a secondary replica if HA is a requirement. Document
  the intent if this is a deliberate read-scale-only configuration.
### H20 — Listener Not Configured
- **Trigger:** No rows in `sys.availability_group_listeners` for this AG
- **Severity:** Info
- **Fix:** Without a listener, applications must connect directly to the primary by server
  name, which requires a connection string change after every failover. Create a listener:
  `ALTER AVAILABILITY GROUP [ag] ADD LISTENER N'ag-listener' (WITH IP ((N'10.0.0.10',
  N'255.255.255.0')), PORT=1433)`. Update application connection strings to use the
  listener DNS name.
### H21 — Read-Only Routing Not Configured
- **Trigger:** A readable secondary exists (`secondary_role_allow_connections_desc =
  ALL` or `READ_ONLY`) AND `read_only_routing_url` IS NULL on that replica
- **Severity:** Info
- **Fix:** Readable secondaries without routing configuration require explicit connection
  string targeting. Configure routing: set `READ_ONLY_ROUTING_URL` on each secondary and
  `READ_ONLY_ROUTING_LIST` on the primary replica so that `ApplicationIntent=ReadOnly`
  connections are automatically directed to a readable secondary.
### H22 — Automatic Seeding Active
- **Trigger:** `seeding_mode_desc = AUTOMATIC` AND a secondary database is in
  `synchronization_state_desc = NOT SYNCHRONIZING` (seeding in progress)
- **Severity:** Info
- **Fix:** Automatic seeding is transferring the database to the secondary. This is normal
  after adding a new replica or database to the AG. Monitor progress with:
  `SELECT * FROM sys.dm_hadr_automatic_seeding`. High network utilization is expected during
  seeding. Seeding of large databases can take hours — plan maintenance windows accordingly.

---

## Output Format

Structure the report exactly as follows. Follow the labeling convention: output labels use
`[C1]`, `[W1]`, `[I1]` — check IDs appear in parentheses after the finding name.

```
## HADR Health Analysis

### Summary
- X Critical, Y Warnings, Z Info
- Availability group: [ag_name]
- Replicas: [list with roles, e.g. NODE1\SQL2019 (PRIMARY), NODE2\SQL2019 (SECONDARY — DISCONNECTED)]
- Highest-risk finding: [check name and ID]

### Critical Issues

### [C1 — H1] Replica Disconnected — NODE2\SQL2019
- **Observed:** connected_state_desc = DISCONNECTED; last_connect_error_number = 35206;
  last_connect_error_description = "The connection attempt to secondary replica 'NODE2\SQL2019'
  timed out."
- **Impact:** All databases on this secondary are no longer receiving log from the primary.
  If this is the only secondary, automatic failover protection is lost.
- **Fix:** Verify network connectivity and SQL Server service state on NODE2\SQL2019. Review
  CLUSTER.LOG for network partition events. Check Windows Event Log for SQL Server service
  failures.

### Warnings

### [W1 — H18] No Automatic Failover Replica
- **Observed:** All replicas have failover_mode_desc = MANUAL
- **Impact:** Primary failure requires manual DBA intervention before any secondary can
  promote, increasing downtime.
- **Fix:** Configure FAILOVER_MODE = AUTOMATIC on a sync-commit secondary after verifying
  WSFC quorum health.

### Info

### [I1 — H21] Read-Only Routing Not Configured — NODE3\SQL2019
- **Observed:** read_only_routing_url IS NULL; secondary is readable
- **Impact:** ApplicationIntent=ReadOnly connections will not be redirected automatically.
- **Fix:** Set READ_ONLY_ROUTING_URL and configure READ_ONLY_ROUTING_LIST on primary.

### Passed Checks

| Check | Result |
|-------|--------|
| H2 — Replica in Resolving State | PASS — no replica in RESOLVING role |
| H3 — Synchronization Unhealthy | PASS — all connected replicas report HEALTHY |
```

Include a **Prioritized Action Order** table after all findings:

```
### Prioritized Action Order

| Priority | Action | Resolves | Effort |
|----------|--------|----------|--------|
| 1 — Immediately | Investigate replica connectivity on NODE2\SQL2019 | C1 | 15 min |
| 2 — Today | Enable AUTOMATIC failover mode on sync secondary | W1 | 30 min |
| 3 — This sprint | Configure read-only routing on NODE3\SQL2019 | I1 | 20 min |
```

## Notes

- When only natural language input is provided, state which columns are missing and apply
  only the checks that can be evaluated from the described values.
- `estimated_data_loss_seconds` and `estimated_recovery_time_seconds` are NULL for async
  replicas that are currently disconnected — note this limitation rather than firing H7/H8.
- `secondary_lag_seconds` is NULL for the primary replica row — skip H9 for primary rows.
- If `log_send_rate` and `redo_rate` are both NULL, the DMV was captured on a secondary
  replica (these columns are populated only on the primary). Note this and advise recapture
  on the primary.
- Do not invent findings not triggered by the rules above.

## Companion Skills

- `/sqlwait-review` — Analyze `HADR_SYNC_COMMIT`, `HADR_WORK_QUEUE`, `HADR_LOGCAPTURE_WAIT`,
  and `HADR_TRANSPORT_SESSION_CHANNEL_LOCK` waits on the primary to quantify the commit
  latency overhead imposed by synchronous replicas (H16, H4).
- `/sqlplan-review` + `/query-store-review` — After a failover or extended lag event,
  applications may use suboptimal plans on the new primary due to cold plan cache or
  parameter sniffing. Run post-failover plan review and Query Store regression checks.
- `/procstats-review` — Identify whether a high-CPU or high-read procedure on the primary
  is generating excessive log volume, contributing to send queue buildup (H11, H14).
- `/tsql-review` — Review T-SQL that runs on a readable secondary to identify implicit
  conversions or non-sargable predicates that add read load and compete with redo threads.