/sqlerrorlog-review

name: sqlerrorlog-review
description: Analyzes SQL Server ERRORLOG files for operational issues, availability group failures, memory pressure, I/O subsystem warnings, and security events. Use this skill whenever a SQL Server instance has experienced unexpected behavior, an AG failover, memory warnings, I/O latency alerts, or abnormal shutdown, and you need a structured timeline of what SQL Server recorded. Applies 33 checks (E1–E33) covering AG health, memory/resource pressure, I/O and storage, startup/shutdown, connectivity, configuration signals, and SQL 2019/2022 modern feature events.
triggers:
  - /sqlerrorlog-review

SQL Server ERRORLOG Review Skill

Purpose

Parse and analyze SQL Server ERRORLOG content to surface operational warnings, high-availability failures, resource pressure signals, security events, and configuration anomalies. Applies 33 checks (E1–E33) across six categories:

• **E1–E8** — AG / High Availability: failovers, lease expiry, replica state changes, synchronization errors
• **E9–E14** — Memory and resource pressure: page allocation failures, OS paging, worker exhaustion, non-yielding schedulers
• **E15–E19** — I/O and storage: slow I/O subsystem, corruption warnings, tempdb exhaustion, log backup gaps, VLF proliferation
• **E20–E24** — Startup, shutdown, and connectivity: abnormal termination, restart cycling, login failure bursts, linked server errors
• **E25–E28** — Configuration and informational: trace flags, unconfigured max memory, log rotation gaps, version end-of-support
• **E29–E33** — SQL 2019/2022 modern features: ADR PVS cleanup stall, IQP DOP feedback, Ledger verification failure, CE feedback model change, Azure Arc agent disconnect

Input

Accept any of:

• **File path** — path to the SQL Server ERRORLOG file (default location:

`C:\Program Files\Microsoft SQL Server\MSSQL<ver>.<inst>\MSSQL\Log\ERRORLOG`)

• **Inline paste** — raw ERRORLOG text pasted directly into chat; partial excerpts are valid
• **Natural language description** — describe the symptoms or paste selected log lines with context

For best results, provide the current ERRORLOG and at least one prior log (`ERRORLOG.1`). When only partial content is available, state which time range is covered.

Capture via T-SQL

-- Read current ERRORLOG (0 = current, 1 = previous, 2 = the one before that)
EXEC xp_readerrorlog 0, 1;          -- SQL Server log, current file
EXEC xp_readerrorlog 1, 1;          -- SQL Server log, previous file

-- Filter to AG-related messages only
EXEC xp_readerrorlog 0, 1, N'availability', NULL, NULL, NULL, N'desc';

-- Filter to a time window (last 2 hours)
DECLARE @start DATETIME = DATEADD(HOUR, -2, GETDATE());
EXEC xp_readerrorlog 0, 1, NULL, NULL, @start, NULL, N'desc';

Column Reference

| Column | Meaning | |--------|---------| | LogDate | Timestamp of the log entry (datetime2 precision) | | ProcessInfo | SPID or system process (e.g., `spid28s`, `Logon`, `Backup`) | | Text | Log message text |

---

Thresholds Reference

| Threshold | Value | Used by | |-----------|-------|---------| | Login failure burst — Warning | > 5 `Login failed` messages in any 5-min window | E22 | | Login failure burst — Critical | > 20 `Login failed` messages in any 5-min window | E22 | | Restart cycling | ≥ 2 SQL Server startup messages within 60 min | E21 | | I/O slow built-in threshold | 15 seconds (SQL Server internal, non-configurable) | E15 | | Log backup overdue — FULL/BULK_LOGGED | > 24 hr since last `Database backed up` entry | E18 | | Log backup overdue — active log pressure signal | > 8 hr when `log_reuse_wait_desc = LOG_BACKUP` | E18 |

---

AG / High Availability Checks (E1–E8)

E1 — AG Failover Event

• **Trigger:** Log contains `is changing roles from` or `is preparing to transition to` or `automatic failover`

in the same entry or within the same minute as a role-change message; also `in response to a request from the Windows Server Failover Cluster`

• **Severity:** Warning — planned failover expected; Critical if the word `automatic` appears

(unplanned loss of primary)

• **Fix:** For unplanned failovers, check E2 (lease expiry) and E6 (health check timeout) as

probable root causes. For planned failovers in unexpected windows, review change-management records. Run `/sqlwait-review` on HADR_SYNC_COMMIT and HADR_WORK_QUEUE waits.

E2 — Lease Expiry

• **Trigger:** Log contains `lease between the availability group and the Windows Server Failover

Cluster has expired` or `The lease of availability group` combined with `has expired`

• **Severity:** Critical — lease expiry is the most common root cause of unplanned AG failovers
• **Fix:** Investigate the time immediately before this entry for E15 (slow I/O), E13

(non-yielding scheduler), or OS-level events. Common causes: storage latency spike causing the sp_server_diagnostics thread to miss its deadline, high CPU starvation, or WSFC network interruption. Increase `LeaseTimeout` in WSFC only as a temporary measure — fix the root cause.

E3 — Replica State Change

• **Trigger:** Log contains `The local replica of availability group ... is changing roles` or

`is preparing to transition to the`

• **Severity:** Warning — state transitions are normal during planned operations; unexpected

transitions during business hours warrant investigation

• **Fix:** Correlate the timestamp with E1 (failover), E2 (lease), or external WSFC events.

If unplanned, check the Windows Event Log and WSFC cluster log for the triggering event.

E4 — AG Database Joining Failure

• **Trigger:** Log contains `Failed to join local availability database` or `The availability

database ... is not in the correct state`

• **Severity:** Critical — the AG database is not receiving redo; secondary is running but not

synchronized, providing false HA coverage

• **Fix:** Run `SELECT * FROM sys.dm_hadr_database_replica_states` to check

`synchronization_state_desc` and `redo_queue_size`. If