# Sqlhadr Review > 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. - Skill: `vanterx/sqlhadr-review` (Agent Skill, multi-file: 8 files) - Install (CLI): `npx skillmds@latest add vanterx/sqlhadr-review` - Raw SKILL.md: https://api.skillmd.com/api/skills/vanterx/sqlhadr-review/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Data & Analytics - Author: vanterx (https://skillmd.com/u/vanterx) - Updated: 2026-09-17 - Page: https://skillmd.com/skills/vanterx/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: ```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 .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 = ` 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//-/analysis.md ← full report output//-/trace.md ← Check Evaluation Log Derive ``: 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 — / # Input: / # Generated: ` trace.md → `# Check Evaluation Log — / # Input: / # Generated: ` 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.