sqlhadr-review · git:20260622.55f8c85 · 2026-06-22 · sha256 f0f40b6aa0b3b7e2
sqlhadr-review git:20260622.55f8c85A
Immutable. This exact content is served forever at /api/v1/blob/f0f40b6aa0b3b7e2.
---
name: sqlhadr-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, a database appears stuck initializing after a failover, or you need a SQL-side snapshot of AG health to complement CLUSTER.LOG and ERRORLOG diagnostics. Applies 27 checks (H1–H28, with H21 retired and merged into sqlag-review F15) covering replica connectivity, data loss risk, recovery time, throughput, configuration, SQL 2016–2022 modern AG features, and seeding/initialization integrity.
triggers:
- /sqlhadr-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 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:
```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, /* 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):
```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:** 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
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.
### Retired — H21 (merged into sqlag-review F15)
- **Status:** This check ID is retired. The condition it covered — a readable secondary with
`read_only_routing_url IS NULL` — is identical to `sqlag-review` F15 (Read-Only Routing URL
Absent on Readable Secondary). Both skills evaluated the same static replica-configuration
columns (`secondary_role_allow_connections_desc`, `read_only_routing_url`) from
`sys.availability_replicas`, with no runtime-only signal available to distinguish them — the
finding belongs to `sqlag-review`, which owns AG *configuration-correctness* checks. Run
`/sqlag-review` for this condition. The H21 ID is left retired rather than reused or
renumbered, to avoid shifting H22–H28 (H28 is cross-referenced by ID from `sqlag-review` F37).
### 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.
## Category 5 — Modern AG Feature Checks (H23–H27)
### H23 — Contained AG Misrouted DML
- **Trigger:** AG has `is_contained = 1` in `sys.availability_groups` AND a contained system database (e.g., `master`, `msdb` within the AG) shows `synchronization_state_desc != SYNCHRONIZED` — SQL 2022+ only; skip if SQL version < 2022
- **Severity:** Warning — Contained AG system databases are not synchronized; DML operations that depend on contained system objects (logins, jobs, agent alerts) may fail on the secondary or after failover
- **Fix:** Investigate why the contained system database is not synchronized: `SELECT * FROM sys.dm_hadr_database_replica_states WHERE database_id = DB_ID('master')`. Resolve blocking transactions and confirm redo queue size. Confirm the contained-AG configuration is intentional via the `is_contained` column in `sys.availability_groups` (= 1 for a contained AG) — there is no `contained_system_databases` column.
### H24 — Cloud Witness Inaccessible
- **Trigger:** `sys.dm_hadr_cluster` shows `quorum_type_desc = CLOUD_WITNESS` AND `quorum_state_desc != 'NORMAL_QUORUM'` — Windows Server 2016+ (Cloud Witness requires WS2016 or later); valid `quorum_state_desc` values are `UNKNOWN_QUORUM_STATE`, `NORMAL_QUORUM`, `FORCED_QUORUM`
- **Severity:** Critical — The Cloud Witness quorum resource is unreachable; the cluster is operating without a functioning quorum witness and is at risk of split-brain or total quorum loss
- **Fix:** Verify connectivity to the Azure Blob Storage endpoint used as the Cloud Witness: `Test-NetConnection -ComputerName <storageaccount>.blob.core.windows.net -Port 443`. Check the Storage Account access key has not been rotated. Validate the Failover Cluster Manager shows the Cloud Witness online. If the witness is permanently unavailable, switch to a File Share Witness or another Cloud Witness account.
### H25 — Parallel Redo Worker Saturation
- **Trigger:** On the secondary, `sys.dm_exec_requests` shows redo workers (`command` IN ('PARALLEL REDO TASK', 'DB STARTUP')) busy/blocked AND `redo_queue_size` / `log_send_queue_size` continues growing — SQL 2016+ parallel redo; skip if SQL version < 2016. (Do **not** use `sys.dm_hadr_physical_seeding_stats` here — that DMV reports automatic-seeding progress, not redo threads.) Corroborate with the `sqlserver.lock_redo_blocked` XE and the `Redo blocked/sec` counter.
- **Severity:** Warning — Parallel Redo workers on the secondary are saturated; the redo queue will grow until the primary throttles log send, increasing recovery time and secondary lag
- **Fix:** Parallel redo threads are allocated automatically (up to 100 instance-wide on SQL 2016-2019; workload-based on SQL 2022+) - confirm the database is not stuck in single-threaded redo and that redo is not blocked by readers (sqlserver.lock_redo_blocked XE, Redo blocked/sec counter). Trace flag 3459 disables parallel redo if serial redo proves faster under contention. Check for lock contention on the secondary: `SELECT * FROM sys.dm_exec_requests WHERE command LIKE '%REDO%'`. Review large transactions on the primary that generate disproportionate redo workload and consider breaking them into smaller batches.
### H26 — Read-Scale Secondary Missing RCSI
- **Trigger:** A readable secondary exists (`secondary_role_allow_connections_desc = READ_ONLY`) AND `SELECT is_read_committed_snapshot_on FROM sys.databases WHERE database_id = <db>` returns 0 on the primary — SQL 2012+
- **Severity:** Warning — Readers on the secondary will encounter locking conflicts with redo threads unless RCSI is enabled; read workloads can block redo, increasing secondary lag
- **Fix:** Enable RCSI on the primary database: `ALTER DATABASE [db] SET READ_COMMITTED_SNAPSHOT ON`. RCSI is propagated to all secondary replicas automatically. Confirm with: `SELECT name, is_read_committed_snapshot_on FROM sys.databases`.
### H27 — AG Without Database-Level Health Detection
- **Trigger:** `sys.availability_groups` shows `db_failover = 0` (DB_FAILOVER = OFF) for an AG where high availability is the stated goal — SQL 2012+
- **Severity:** Info — Without DB_FAILOVER = ON, a database-level failure (e.g., a database going suspect or offline) will not trigger AG failover; the AG remains online with a failed database silently
- **Fix:** Enable database-level health detection: `ALTER AVAILABILITY GROUP [ag] SET (DB_FAILOVER = ON)`. Confirm the application can tolerate transient failovers triggered by database-level failures before enabling this option.
## Category 6 — Seeding and Initialization Integrity (H28)
### H28 — Secondary Database Stuck in INITIALIZING State
- **Trigger:** `synchronization_state_desc = INITIALIZING` persists across repeated captures with
no advancing redo/hardened-LSN progress, or `INITIALIZING` is observed on a database
immediately following a failover involving that replica
- **Severity:** Critical
- **Fix:** Microsoft documents that `INITIALIZING` is the undo phase in which the transaction log
required to catch a secondary up to the undo LSN is still being shipped and hardened — and
explicitly warns that forcing failover to a secondary while its database is in this state
leaves the database unable to start as primary; it must either reconnect as a secondary or
have log backups applied. Do not force failover onto a replica with a database in
`INITIALIZING`. If a database is already stuck in this state post-failover, check
`sys.dm_hadr_database_replica_states.last_redone_lsn` for any progress, then either let it
reconnect to a healthy primary to resume normal log streaming, or restore from a log-backup
chain to bring it current. Also check whether this database was onboarded with
`seeding_mode_desc = AUTOMATIC` left active alongside a manual backup/restore workflow:
`SEEDING_MODE` is evaluated dynamically at join time, so both paths can target the same
database and conflict. (Microsoft Learn does not document this combination as producing a
corrupt secondary — combining the methods is supported — but a conflicting/failed seed can
leave a database that never cleanly reaches `SYNCHRONIZED`; see `sqlag-review` F37.)
---
## Version-Aware Check Suppression
If the SQL Server version is stated by the user, read `VERSION_COMPATIBILITY.md` (`~/.claude/skills/VERSION_COMPATIBILITY.md` if installed, or `skills/VERSION_COMPATIBILITY.md` from the repo). If unavailable, skip silently. For checks whose minimum version exceeds the instance version: verbose mode → log as `SKIP (version: requires SQL 20XX+, instance is SQL 20YY)`; standard report → omit entirely. Do not suppress `NOT ASSESSED` rows from missing input — only suppress version-inapplicable checks.
---
## 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 — H27] AG Without Database-Level Health Detection
- **Observed:** db_failover = 0 on an AG where high availability is the stated goal
- **Impact:** A database-level failure (suspect/offline) will not trigger AG failover; the
AG remains online with a failed database silently.
- **Fix:** Enable database-level health detection: `ALTER AVAILABILITY GROUP [ag] SET
(DB_FAILOVER = ON)` after confirming the application tolerates transient failovers.
### 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 | Enable DB_FAILOVER on the AG | I1 | 10 min |
---
*Analyzed by: [state the AI model and version you are running as, e.g. "Claude Sonnet 4.6", "DeepSeek R1", "GPT-4o"] · [current date and time in the user's local timezone, or UTC if timezone is unknown, e.g. "2026-05-16 20:15 NZST"]*
```
## 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.
---
### Section: Output Filters (--brief / --critical-only)
**`--brief`** — Omit the Passed Checks table and attribution footer. Output the Summary, Findings, and Prioritized Fix Sequence sections only. Use when a quick scan of what fired is all that's needed.
**`--critical-only`** — Suppress Warning and Info findings. Show only Critical findings. The Passed Checks table is also omitted. Use when triaging an incident and only actionable blockers matter.
Both flags can be combined: `--brief --critical-only` produces the Summary section plus Critical findings only.
When neither flag is present, produce the full report as documented above.
---
### Section: Verbose Output (--verbose)
When the user's request includes `--verbose`, `--trace`, or the word `verbose`:
**1. Append a `## Check Evaluation Log` section** after the Passed Checks table.
Include one row for every check in this skill's ruleset, in check-ID order:
| Check | Evidence | Threshold | Result |
|-------|----------|-----------|--------|
| [ID — Name] | [key attribute(s) and value found, or "absent"] | [threshold or condition] | PASS / **FIRE → [severity]** / NOT ASSESSED |
Result conventions:
- `PASS` — attribute present, threshold not met
- `**FIRE → Critical/Warning/Info**` — threshold met; bold to distinguish from passes
- `NOT ASSESSED` — required attribute absent from input
**2. Save both files** to the current working directory using the Write tool:
output/<skill-name>/<YYYY-MM-DD-HHmmss>-<input-prefix>/analysis.md ← full report
output/<skill-name>/<YYYY-MM-DD-HHmmss>-<input-prefix>/trace.md ← Check Evaluation Log
Derive `<input-prefix>`:
1. Filename stem if a file path was provided (e.g. `horrible.sqlplan` → `horrible`)
2. First meaningful identifier from the artifact (top wait type, first table name, procedure name, etc.)
3. Fallback: `run`
Sanitize: alphanumeric + hyphens/underscores only, max 32 chars.
File headers:
analysis.md → `# Analysis — <skill-name> / # Input: <first 80 chars> / # Generated: <UTC timestamp>`
trace.md → `# Check Evaluation Log — <skill-name> / # Input: <first 80 chars> / # Generated: <UTC timestamp>`
Create directories as needed. When `--verbose` is not present, write nothing to disk.
---
## 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` + `/sqlquerystore-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.
- `/sqlprocstats-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.
- `/sqlmigration-review` — Before seeding an AG as a migration mechanism, run this skill to
confirm the target edition/version supports the planned replica count and topology; it
dispatches AG runtime-health overlap back to this skill.
- `/sqlag-review` — If H28 fires (a database stuck in INITIALIZING), check F37 for the
configuration-level root cause: `seeding_mode_desc = AUTOMATIC` left active on a replica
during what was intended as a manual-restore workflow. Also run `/sqlag-review` for F15
(read-only routing URL absent on a readable secondary) — this is the canonical check for
that condition; the equivalent `sqlhadr-review` check (H21) is retired.
- **mssql-performance-review** — Orchestrator that routes mixed artifacts to multiple specialised skills (this one included), runs an adversarial root-cause check, and produces a single consolidated report with evidence chain, risk-rated fixes, and rollback. Use when you have several artifact types together or describe a symptom without knowing which skill to run.