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.\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 HADRSYNCCOMMIT and HADRWORKQUEUE 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 spserverdiagnostics 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 redo queue is growing, check disk I/O on the secondary. If the database is in NOT SYNCHRONIZING, re-join: ALTER DATABASE [db] SET HADR AVAILABILITY GROUP = [ag_name].

E5 — Data Synchronisation Suspended

• Trigger: Log contains Synchronization of this database ... has been suspended or

Data movement for availability database ... has been suspended

• Severity: Warning — a suspended database is not receiving log records; RPO clock is running
• Fix: Identify whether the suspension was manual (ALTER DATABASE ... SET HADR SUSPEND) or

automatic (error-triggered). Check for E15/E16 (I/O or corruption) causing automatic suspension. Resume: ALTER DATABASE [db] SET HADR RESUME. Monitor redo queue.

E6 — AG Health Check Timeout

• Trigger: Log contains `availability group ... has failed to take necessary action within

the time allotted or The availability group ... exceeded the health-check timeout`

• Severity: Critical — health-check failure directly precedes automatic failover; this entry

combined with E1 confirms the full failover sequence

• Fix: Identify what the primary was doing at the time. E13 (non-yielding scheduler) or E9

(page allocation failure) are common co-occurrences. The HealthCheckTimeout WSFC property controls sensitivity — do not increase it without fixing the underlying responsiveness problem.

E7 — Redo Thread Error

• Trigger: Log contains An error occurred in the redo thread for database or

Redo thread for database ... encountered error

• Severity: Critical — the secondary redo thread has failed; the secondary is no longer

applying log records and RPO is accumulating

• Fix: Note the error number in the log message. Common causes: corruption on the secondary

(check E16), log record version mismatch after an upgrade, or disk full on secondary. For disk-full, free space and resume synchronization. For corruption, restore the secondary from a backup and re-seed.

E8 — Secondary Not Synchronising

• Trigger: Log contains Waiting for redo catch-up or mentions secondary redo queue in a

warning context; or log send queue appearing repeatedly with growing values

• Severity: Warning — secondary is lagging; failover to this replica would result in data

loss proportional to the redo queue depth

• Fix: Check network bandwidth between primary and secondary. Run

SELECT redo_queue_size, redo_rate FROM sys.dm_hadr_database_replica_states. If redo rate 0, Error: 823 (OS-level I/O failure — Windows API returned an error), Error: 824 (logical consistency check failure — Windows I/O succeeded but SQL detected corruption on the page), or Error: 825 (read succeeded after retry — transient storage issue; Warning severity; indicates potential hardware degradation)

• Severity: Critical for Msg 823 and Msg 824 (data corruption confirmed); Warning for Msg 825

(read ultimately succeeded but is a precursor to harder failures — investigate storage hardware immediately); Critical if DBCC CHECKDB reports allocation or consistency errors

• Fix: Run DBCC CHECKDB ([database]) WITH NO_INFOMSGS immediately to assess scope. Do

not attempt to repair until a current, verified backup exists. For REPAIR_ALLOW_DATA_LOSS, treat it as a last resort — restore from backup is always preferable. Investigate E15 (I/O latency) and storage hardware health as root causes.

E17 — TempDB Space Exhaustion

• Trigger: Log contains Could not allocate space combined with in database 'tempdb'

or tempdb is full or tempdb ran out of space

• Severity: Critical — queries requiring temporary space (sorts, hashes, spools, row

versioning) are failing; error 1105 is returned to applications

• Fix: Immediately: DBCC SHRINKFILE on tempdb data files to recover any unused allocated

space, or add a tempdb data file. Long term: investigate which query is consuming tempdb (check sys.dm_db_session_space_usage). Run /sqlplan-review for N41–N43 (spill operators). Consider pre-allocating tempdb to expected working size at startup.

E18 — Log Backup Overdue

• Trigger: Gap between consecutive Database backed up entries for the same database

exceeds the threshold for that recovery model. For databases in FULL or BULK_LOGGED recovery, flag if the gap exceeds 24 hours; flag more urgently if log backup entries are absent while other evidence suggests active transaction log growth

• Severity: Warning — log space will grow unboundedly without log backups; in a FULL

recovery database, the log cannot be truncated until backed up

• Fix: Run a log backup immediately: BACKUP LOG [database] TO DISK = N'path\logbackup.bak'.

Verify the SQL Agent log backup job is scheduled and enabled. Check sys.databases column log_reuse_wait_desc — if LOG_BACKUP, the log is waiting for a backup to allow truncation.

E19 — VLF Proliferation Signal

• Trigger: Log shows repeated autogrow events on transaction log files (multiple autogrow

completions in the log window), or the database log has grown significantly between ERRORLOG entries — inferred from repeated log file path growth messages

• Severity: Info — excessive VLFs degrade recovery time and log-backup performance; auto-grow

events indicate the log was not sized for the workload

• Fix: Shrink and pre-size the log: set the initial log file size to cover expected working

set and disable autogrow on the log (or set a large, infrequent growth increment). Run DBCC LOGINFO ([database]) to count current VLFs — if > 1,000, shrink and re-expand in one step. Align with E18 (log backup cadence) to ensure the log truncates regularly.

---

Startup, Shutdown, and Connectivity Checks (E20–E24)

E20 — Abnormal Shutdown

• Trigger: Log contains SQL Server is terminating or SQL Server has encountered combined

with stack dump or shutdown messages, without a preceding graceful shutdown marker (SQL Server is terminating due to a system shutdown request at the end of the prior log file)

• Severity: Critical — the instance crashed rather than shut down cleanly; uncommitted

transactions were rolled back on restart; any in-flight work is lost

• Fix: Check the Windows Event Log (Application and System sources) for the crash

timestamp. Look for a dump file in the SQL Server Log directory. If the crash occurred mid-transaction in an AG, check whether secondary databases advanced beyond the primary (split-brain risk). Engage Microsoft Support with the minidump if the crash is reproducible.

E21 — Repeated Restarts

• Trigger: ERRORLOG or combined ERRORLOG + ERRORLOG.1 contains ≥ 2 SQL Server startup

messages (lines containing SQL Server is starting or This instance of SQL Server last reported using a process ID) within a 60-minute window

• Severity: Critical — the instance is crash-looping; each restart drops all plan cache and

connection state; applications experience repeated connection failures

• Fix: Check E20 (abnormal shutdown) for the crash cause between restarts. If the instance

is restarting due to a failed startup condition (e.g., tempdb creation failure, master database corruption, or xp_cmdshell misconfiguration), resolve the startup error first. Enable Windows Automatic Recovery only after identifying the underlying fault.

E22 — Login Failure Burst

• Trigger: Count of Login failed entries exceeds the threshold within a 5-minute rolling

window — see Thresholds Reference for Warning and Critical levels

• Severity: Warning if > 5 failures in 5 min; Critical if > 20 failures in 5 min
• Fix: Identify the ClientConnectionID and source IP in the failure messages. A burst from

one account likely indicates a misconfigured application connection string after a password rotation. A burst from many accounts may indicate a brute-force attempt. For brute-force: enable SQL Server Audit or Extended Events on Failed Logins and block the source IP at the network layer. Ensure LOGINAUDIT is set to Failed logins only or Both in Server properties so future bursts appear in the ERRORLOG.

E23 — Linked Server Error

• Trigger: Log contains OLE DB provider combined with reported an error or

Cannot obtain the required interface for a linked server provider

• Severity: Warning — distributed queries or cross-server stored procedures using this

linked server will fail until the provider error is resolved

• Fix: Identify the linked server name and provider from the error text. Common causes:

target server unavailable, credential expiry, or OLE DB provider version mismatch. Test connectivity: EXEC sp_testlinkedserver [linked_server_name]. If the provider is outdated, update it on the SQL Server host.

E24 — Connectivity Error

• Trigger: Log contains `A connection was successfully established with the server, but

then an error occurred during the login process or The connection has been lost or A network-related or instance-specific error` in the ERRORLOG (as opposed to the client)

• Severity: Warning — SQL Server is logging errors from its own outbound connections

(linked servers, distributed queries, SSISDB, mail, replication) or from incoming connections that dropped after TCP establishment

• Fix: Correlate the timestamp with E22 (login failures), network infrastructure changes,

or TLS/SSL certificate renewals. If TLS handshake appears in the message, verify that the certificate in use has not expired and that the client supports the negotiated protocol.

---

Configuration and Informational Checks (E25–E28)

E25 — Trace Flag Active

• Trigger: Log contains Trace flag combined with is set or was enabled at startup

in startup messages

• Severity: Info — trace flags change engine behavior; document intent and verify they

are still appropriate for the current SQL Server version

• Fix: List all active trace flags: DBCC TRACESTATUS(-1). Common production trace flags

and their intent: 1117/1118 (tempdb allocation — superseded in 2016+), 3226 (suppress successful backup log entries), 4199 (QO hotfixes). Remove trace flags that are no longer needed or that apply to behaviour fixed in a later CU.

E26 — Max Server Memory Default

• Trigger: Log contains startup line showing max server memory = 2147483647 MB, or the

instance has been running with the default (unlimited) memory configuration — inferred from startup messages or the absence of an explici

…

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: vanterx
• Source: vanterx/mssql-performance-skills
• License: MIT

Install and usage instructions live in the source repository linked above.