Part of the DBA-Tools Project.
Group the Error Log Instead of Scrolling It
The SQL Server error log is where the instance tells you what’s wrong, but on a busy server it’s also where hundreds of routine startup, backup, and checkpoint messages bury the handful of lines that actually matter. Scrolling xp_readerrorlog output looking for the one memory-pressure warning or the one login failure among pages of noise is slow, and it’s easy to miss something on a bad day when there’s more log than usual.
Get-ErrorLogPatterns reads the current error log and groups every entry into one of nine categories (memory pressure, login failure, backup/restore, IO issue, corruption/integrity, database state, auto-growth, deadlock, or generic error/warning) with a count, a first/last-seen timestamp, and one sample message per category. It turns “read the whole log” into “scan nine rows.”
Why Error Log Patterns Matters
- Volume hides signal. A single genuine memory-pressure warning is easy to miss in a log full of routine backup-completion and checkpoint lines. Categorized counts surface it immediately.
- First-seen and last-seen timestamps show duration, not just occurrence. A memory pressure category spanning several hours is a different problem than one that appeared once and stopped.
- It’s a fast first move during an incident. Before diving into DMVs, a categorized error log pass tells you whether the instance itself has been reporting a problem, and for how long.
- The categorization is keyword-based, not perfect, and that matters for how you read the results (more on this below).
When to Run This Script
- As part of a daily or weekly health pass, alongside Get Recent Error Log Entries
- First, during an active incident, to get an instant read on whether the instance has been logging anything relevant in the recent window
- After a bad night, to confirm whether an issue you’re chasing left a trail in the error log at all
- When investigating a specific category (memory, IO, corruption) to pull a fast baseline before digging into the matching DMVs
The Script
Run the following script against your SQL Server instance.
/*
Script Name : Get-ErrorLogPatterns
Category : monitoring
Purpose : Reads the current SQL Server error log and groups entries by category — surfaces memory pressure, login failures, IO issues, corruption warnings, and auto-growth events without scrolling through raw entries.
Author : Peter Whyte (https://sqldba.blog)
Requires : VIEW SERVER STATE (for xp_readerrorlog via sysadmin or securityadmin)
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
/* ── How many hours back to read (default 24) ───────────────────────────── */
DECLARE @HoursBack INT = 24;
/* ─────────────────────────────────────────────────────────────────────────── */
IF OBJECT_ID('tempdb..#ErrLog') IS NOT NULL DROP TABLE #ErrLog;
CREATE TABLE #ErrLog (
LogDate DATETIME NOT NULL,
ProcessInfo NVARCHAR(50),
Txt NVARCHAR(4000)
);
DECLARE @StartDate DATETIME = DATEADD(HOUR, -@HoursBack, GETDATE());
INSERT INTO #ErrLog
EXEC xp_readerrorlog 0, 1, NULL, NULL, @StartDate, NULL, N'desc';
SELECT
CASE
WHEN Txt LIKE '%paged out%'
OR Txt LIKE '%virtual address space%'
OR Txt LIKE '%out of memory%'
OR Txt LIKE '%cannot allocate%' THEN 'Memory Pressure'
WHEN Txt LIKE '%login failed%'
OR Txt LIKE '%18456%'
OR Txt LIKE '%password%incorrect%' THEN 'Login Failure'
WHEN Txt LIKE '%Backup%'
OR Txt LIKE '%BACKUP%'
OR Txt LIKE '%backup of database%'
OR Txt LIKE '%RESTORE%' THEN 'Backup / Restore'
WHEN Txt LIKE '%I/O%'
OR Txt LIKE '%stall%'
OR Txt LIKE '%stalled%'
OR Txt LIKE '%disk%' THEN 'IO Issue'
WHEN Txt LIKE '%corrupt%'
OR Txt LIKE '% 824 %'
OR Txt LIKE '% 823 %'
OR Txt LIKE '% 825 %'
OR Txt LIKE '%checkdb%' THEN 'Corruption / Integrity'
WHEN Txt LIKE '%suspect%'
OR Txt LIKE '%offline%'
OR Txt LIKE '%emergency%'
OR Txt LIKE '%recovery%' THEN 'Database State'
WHEN Txt LIKE '%autogrow%'
OR Txt LIKE '%Auto-grow%'
OR Txt LIKE '% grew %'
OR Txt LIKE '%Autogrow%' THEN 'Auto-Growth'
WHEN Txt LIKE '%deadlock%' THEN 'Deadlock'
WHEN Txt LIKE '%Error%'
OR Txt LIKE '%error%'
OR Txt LIKE '%failed%' THEN 'Error / Failure'
WHEN Txt LIKE '%Warning%'
OR Txt LIKE '%warning%' THEN 'Warning'
ELSE 'Informational'
END AS category,
COUNT(*) AS occurrences,
MIN(LogDate) AS first_seen,
MAX(LogDate) AS last_seen,
LEFT(MAX(Txt), 200) AS sample_message
FROM #ErrLog
GROUP BY
CASE
WHEN Txt LIKE '%paged out%'
OR Txt LIKE '%virtual address space%'
OR Txt LIKE '%out of memory%'
OR Txt LIKE '%cannot allocate%' THEN 'Memory Pressure'
WHEN Txt LIKE '%login failed%'
OR Txt LIKE '%18456%'
OR Txt LIKE '%password%incorrect%' THEN 'Login Failure'
WHEN Txt LIKE '%Backup%'
OR Txt LIKE '%BACKUP%'
OR Txt LIKE '%backup of database%'
OR Txt LIKE '%RESTORE%' THEN 'Backup / Restore'
WHEN Txt LIKE '%I/O%'
OR Txt LIKE '%stall%'
OR Txt LIKE '%stalled%'
OR Txt LIKE '%disk%' THEN 'IO Issue'
WHEN Txt LIKE '%corrupt%'
OR Txt LIKE '% 824 %'
OR Txt LIKE '% 823 %'
OR Txt LIKE '% 825 %'
OR Txt LIKE '%checkdb%' THEN 'Corruption / Integrity'
WHEN Txt LIKE '%suspect%'
OR Txt LIKE '%offline%'
OR Txt LIKE '%emergency%'
OR Txt LIKE '%recovery%' THEN 'Database State'
WHEN Txt LIKE '%autogrow%'
OR Txt LIKE '%Auto-grow%'
OR Txt LIKE '% grew %'
OR Txt LIKE '%Autogrow%' THEN 'Auto-Growth'
WHEN Txt LIKE '%deadlock%' THEN 'Deadlock'
WHEN Txt LIKE '%Error%'
OR Txt LIKE '%error%'
OR Txt LIKE '%failed%' THEN 'Error / Failure'
WHEN Txt LIKE '%Warning%'
OR Txt LIKE '%warning%' THEN 'Warning'
ELSE 'Informational'
END
ORDER BY occurrences DESC;
DROP TABLE #ErrLog;
Reads the current error log via xp_readerrorlog for a configurable window (@HoursBack, default 24), buckets every entry into one of nine categories by keyword match, then returns count, first-seen, last-seen, and one sample message per category, ordered by occurrence count descending.
How To Run From The Repo
Clone DBA Tools, initialize and run the script:
# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools
# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1
# Categorized error log summary for the last 24 hours:
.\run.ps1 Get-ErrorLogPatterns
# To run against a remote sql server:
.\run.ps1 Get-ErrorLogPatterns -ServerInstance SQLSERVER01
This script lives in the repo at:
sql/monitoring/error-log/Get-ErrorLogPatterns.sqlpowershell/wrappers/monitoring/error-log/Get-ErrorLogPatterns.ps1
Example Output
Real output captured 2026-07-31 07:18 against ..
Understanding the Results
The lab instance’s own output has a genuine, previously-documented finding: the Memory Pressure category, 7 occurrences over roughly half an hour, sample message “A significant part of SQL Server process memo[ry paged out]…”. This is the same sustained memory pressure covered in Get Recent Error Log Entries, this instance runs on Peter’s own 8GB working machine alongside Docker and other dev tools, not a dedicated lab box, so the pressure is real and expected here. It’s exactly the kind of finding this script is built to surface fast.
It also shows the categorization’s real limitation clearly: Corruption / Integrity has 6 occurrences, but the sample message is a routine CHECKDB completion notice (“CHECKDB for database… finished…”), not an actual corruption error. The %checkdb% keyword match catches both a genuine 823/824/825 error and a normal “CHECKDB completed successfully” line, because both mention CHECKDB. Same story for Database State, which caught “Recovery is complete” (a normal startup message) via the %recovery% pattern. Read the sample_message column before reacting. A high count in a category is a prompt to look, not a verdict.
Categories worth treating as near-automatic priorities regardless of count: Login Failure (any nonzero count is worth a look, especially a spike), and Memory Pressure or IO Issue sustained across a long first-seen to last-seen span rather than a single blip.
Best Practices
- Read
sample_message, not just the count, before deciding a category needs action. Keyword matching means Corruption/Integrity and Database State both include benign entries alongside real ones. - Widen
@HoursBackwhen investigating an incident from the day before rather than assuming the default 24-hour window covers it. - Run this alongside Get Recent Error Log Entries rather than instead of it: this script tells you what categories exist and how often, that one gives you the raw entries in full.
Related Scripts
You may also find these scripts useful:
Summary
Get-ErrorLogPatterns won’t replace reading the full error log during a real incident, but it’s the fastest way to know whether that’s necessary in the first place. Nine categories, real counts, real timestamps, and one sample line each, scanned in the time it takes to read a short table rather than pages of raw log entries.
The one habit worth building alongside it: always check the sample message before treating a count as a verdict. The categorization is a keyword match, not true classification, and this post’s own real output shows exactly why that distinction matters.
Leave a Reply