/sqlclusterlog-review

name: sqlclusterlog-review
description: Analyzes Windows Server Failover Cluster (WSFC) CLUSTER.LOG files for Always On Availability Group root-cause diagnosis. Use this skill when an availability group has gone offline, a failover occurred unexpectedly, or a node was evicted, and you need to identify the WSFC-level cause that SQL Server DMVs cannot see. Applies 30 checks (L1–L30) covering lease timeouts, health check failures, quorum loss, node eviction, network partition, RHS crashes, AG resource transitions, Cloud Witness, Azure Arc, and Contained AG.
triggers:
  - /sqlclusterlog-review

WSFC Cluster Log Review Skill

Purpose

Analyze Windows Server Failover Cluster (WSFC) CLUSTER.LOG files to diagnose Always On Availability Group failures at the cluster level — the layer below SQL Server DMVs. Applies 30 checks (L1–L30) across five categories:

• **L1–L8** — File-wide patterns: lease timeouts, health check failures, RHS crashes, error

bursts, repeated failover cycling, quorum loss, node eviction, log time gaps

• **L9–L17** — AG resource checks: offline transitions, SQL connectivity loss, forced failovers,

long pending states, DLL init failures, API timeouts, cascade failures, primary role loss, replica disconnection

• **L18–L22** — Network and node: partition/split-brain, NIC failure, heartbeat timeout,

witness failure, node isolation

• **L23–L25** — Configuration signals: VerboseLogging=0, SeparateMonitor absent, incomplete

node coverage

• **L26–L30** — Modern cluster features: Cloud Witness timeout, Azure Arc agent disconnect,

Contained AG system database offline, cross-subnet probe failure, sp_server_diagnostics warning

Input

Accept any of:

• **File path** — path to `CLUSTER.LOG` (live log: `C:\Windows\Cluster\cluster.log`; log generated via `Get-ClusterLog`: `C:\Windows\Cluster\Reports\CLUSTER.LOG`)
• **Inline paste** — raw CLUSTER.LOG content pasted directly into chat
• **Natural language description** — describe symptoms ("the AG went offline at 14:32,

SQL error log shows lease expiry")

For full analysis, the log should cover at least the 10 minutes before the incident and include entries from all cluster nodes. If only a partial extract is available, note which time range and nodes are covered and flag L25 if node coverage appears incomplete.

Log Entry Format

WSFC log entries follow this pattern:

<tid>.<pid>::<YYYY>/<MM>/<DD>-<HH>:<MM>:<SS>.<ms> <LEVEL> [<COMPONENT>] <message>

Key components:

• `[RES]` — Resource DLL host (hadrres.dll operations)
• `[hadrag]` — AG-specific resource agent inside RES
• `[RHS]` — Resource Hosting Subsystem (manages RES process lifecycle)
• `[RCM]` — Resource Control Manager (orchestrates state transitions)
• `[NM]` — Network Manager
• `[NODE]` — Node membership and heartbeat
• `ERR` / `WARN` / `INFO` — Severity prefixes in log lines

---

Thresholds Reference

| Threshold | Value | Used by | |-----------|-------|---------| | Error burst window | >10 ERR lines in 5 min → Critical; >5 → Warning | L4 | | Failover cycling | ≥3 group moves in 30 min → Critical; ≥2 → Warning | L5 | | Log time gap | >30 min → Critical; >5 min → Warning | L8 | | Pending state duration | >120 sec → Critical; >30 sec → Warning | L12 | | Lease timeout | 20 sec (SQL Server default LeaseTimeout — distinct from HealthCheckTimeout) | L1 | | Health check timeout | 30 sec (SQL Server default HealthCheckTimeout for sp_server_diagnostics) | L2 | | Heartbeat timeout | Missed heartbeats reach the threshold — WSFC default SameSubnetThreshold/CrossSubnetThreshold is **version-dependent**: 20 (Windows Server 2019), 10 (WS2016), 5 (WS2008–2012 R2) | L20 |

---

File-Wide Pattern Checks (L1–L8)

Evaluate these first — they reveal root causes that explain all downstream AG failures.

L1 — Lease Timeout

• **Trigger:** Log contains `[hadrag] Lease Thread terminated`, `lease time expired`, `HealthCheckTimeout` associated with a lease expiry message, or `LeaseExpired` in `[RES]` or `[hadrag]` context
• **Severity:** Critical — lease expiry causes an immediate AG resource failure with no grace period
• **Fix:** Lease timeout indicates the SQL Server health check thread did not respond within the lease window (see Thresholds Reference). Root causes: (1) SQL Server scheduler starvation — check for long-running queries blocking the health thread; (2) memory pressure causing paging — review sys.dm_os_memory_clerks; (3) storage I/O latency > 10 ms on the system drive — check Windows Performance Monitor; (4) if underlying cause is none of the above, increase `sp_server_diagnostics` timeout via `CLUSTER_DIAGNOSTICS_TIMEOUT` or raise `HealthCheckTimeout` in the AG resource properties.

L2 — Health Check Failure

• **Trigger:** Log contains `IsAlive check failed`, `LooksAlive check failed`, `HealthCheckTimeout`, or `sp_server_diagnostics` returning a failure state (`STATE = 3` or `STATE = 4`) in `[RES]`/`[hadrag]` messages
• **Severity:** Critical — consecutive health check failures trigger resource restart or failover
• **Fix:** Identify whether LooksAlive or IsAlive failed. LooksAlive failures (process-level ping) indicate SQL Server process termination or severe hangs. IsAlive failures (sp_server_diagnostics query) indicate scheduler starvation, I/O hangs, or insufficient health check timeout. Capture `sys.dm_os_ring_buffers` for the incident time and review the SQL ERRORLOG for the matching `SPID N` error.

L3 — RHS Process Crash

• **Trigger:** Log contains `RHS process terminated`, `RHS.EXE terminated unexpectedly`, `creating new RHS process`, `rhs.exe` exit in `[RHS]` context, or `RHS exiting` / `unhandled exception in RHS`
• **Severity:** Critical — RHS crash causes all resources hosted in that process to go offline
• **Fix:** RHS crash is a Windows-level failure, not SQL Server. Capture the Windows Application and System event logs at the incident time. Look for `Event ID 1146` (RHS terminated) and corresponding Dr. Watson / crash d