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	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.