Error Log Analysis
This query is something I’ve been tweaking to get info from the SQL Error Logs, broken down into groups of errors to see what is plaguing the system, and can be run on systems 2012 to current. I make a registered server group of all my servers, and then run this query against them all.
SET NOCOUNT ON;
DECLARE @ServerName NVARCHAR(128) = @@SERVERNAME;
-- Create temp table to store error log results
IF OBJECT_ID('tempdb..#ErrorLogRaw') IS NOT NULL DROP TABLE #ErrorLogRaw;
CREATE TABLE #ErrorLogRaw (
LogDate DATETIME,
ProcessInfo NVARCHAR(50),
[Text] NVARCHAR(4000)
);
-- Create analysis table
IF OBJECT_ID('tempdb..#ErrorAnalysis') IS NOT NULL DROP TABLE #ErrorAnalysis;
CREATE TABLE #ErrorAnalysis (
ServerName NVARCHAR(128),
LogDate DATETIME,
ProcessInfo NVARCHAR(50),
ErrorMessage NVARCHAR(4000),
ErrorLevel INT,
ErrorNumber INT,
ErrorCategory NVARCHAR(100)
);
-- Read current error log
INSERT INTO #ErrorLogRaw (LogDate, ProcessInfo, [Text])
EXEC xp_readerrorlog 0, 1;
-- Read previous error log
INSERT INTO #ErrorLogRaw (LogDate, ProcessInfo, [Text])
EXEC xp_readerrorlog 1, 1;
-- Analyze and categorize errors
INSERT INTO #ErrorAnalysis (ServerName, LogDate, ProcessInfo, ErrorMessage, ErrorLevel, ErrorNumber, ErrorCategory)
SELECT
@ServerName,
LogDate,
ProcessInfo,
[Text],
CASE
WHEN [Text] LIKE '%Error:%' THEN 16
WHEN [Text] LIKE '%Warning:%' THEN 14
WHEN [Text] LIKE '%Severity:%' THEN
CASE
WHEN PATINDEX('%Severity: [0-9][0-9]%', [Text]) > 0 THEN
TRY_CAST(SUBSTRING([Text], PATINDEX('%Severity: [0-9][0-9]%', [Text]) + 10, 2) AS INT)
WHEN PATINDEX('%Severity: [0-9]%', [Text]) > 0 THEN
TRY_CAST(SUBSTRING([Text], PATINDEX('%Severity: [0-9]%', [Text]) + 10, 1) AS INT)
ELSE 10
END
ELSE 10
END,
-- Skip error number extraction to avoid conversion issues
NULL,
CASE
-- Critical System Errors
WHEN [Text] LIKE '%deadlock%' OR [Text] LIKE '%1205%' THEN 'Deadlock'
WHEN [Text] LIKE '%timeout%' OR [Text] LIKE '%1222%' THEN 'Query Timeout'
WHEN [Text] LIKE '%out of memory%' OR [Text] LIKE '%701%' OR [Text] LIKE '%802%' THEN 'Memory Pressure'
WHEN [Text] LIKE '%insufficient%memory%' OR [Text] LIKE '%8645%' THEN 'Memory Pressure'
WHEN [Text] LIKE '%disk%space%' OR [Text] LIKE '%1105%' OR [Text] LIKE '%9002%' THEN 'Disk Space Issues'
WHEN [Text] LIKE '%corruption%' OR [Text] LIKE '%824%' OR [Text] LIKE '%823%' OR [Text] LIKE '%825%' THEN 'Data Corruption'
WHEN [Text] LIKE '%checkdb%' AND [Text] LIKE '%error%' THEN 'Data Corruption'
WHEN [Text] LIKE '%backup%fail%' OR [Text] LIKE '%3201%' OR [Text] LIKE '%3202%' THEN 'Backup Failures'
WHEN [Text] LIKE '%restore%fail%' OR [Text] LIKE '%3203%' THEN 'Restore Failures'
WHEN [Text] LIKE '%login fail%' OR [Text] LIKE '%18456%' THEN 'Login Failures'
WHEN [Text] LIKE '%blocking%' OR [Text] LIKE '%1204%' THEN 'Blocking Issues'
WHEN [Text] LIKE '%autogrow%' OR [Text] LIKE '%5144%' THEN 'Database Autogrow'
WHEN [Text] LIKE '%mirroring%' AND ([Text] LIKE '%error%' OR [Text] LIKE '%fail%') THEN 'Mirroring Issues'
WHEN [Text] LIKE '%availability%group%' AND [Text] LIKE '%error%' THEN 'Availability Group Issues'
WHEN [Text] LIKE '%always%on%' AND [Text] LIKE '%error%' THEN 'Availability Group Issues'
WHEN [Text] LIKE '%service broker%' AND [Text] LIKE '%error%' THEN 'Service Broker Issues'
WHEN [Text] LIKE '%tempdb%' AND ([Text] LIKE '%full%' OR [Text] LIKE '%space%') THEN 'TempDB Issues'
WHEN [Text] LIKE '%log%full%' OR [Text] LIKE '%9002%' THEN 'Transaction Log Full'
WHEN [Text] LIKE '%suspect%' AND [Text] LIKE '%database%' THEN 'Database Suspect'
WHEN [Text] LIKE '%Error:%' OR ([Text] LIKE '%Severity:%' AND [Text] NOT LIKE '%Severity: 10%') THEN 'General Errors'
WHEN [Text] LIKE '%Warning:%' THEN 'Warnings'
WHEN [Text] LIKE '%failed%' AND [Text] NOT LIKE '%login%' THEN 'General Failures'
ELSE 'Information'
END
FROM #ErrorLogRaw
WHERE LogDate >= DATEADD(day, -7, GETDATE()) -- Last 7 days
AND (
[Text] LIKE '%Error:%'
OR [Text] LIKE '%Severity:%'
OR [Text] LIKE '%deadlock%'
OR [Text] LIKE '%timeout%'
OR [Text] LIKE '%memory%'
OR [Text] LIKE '%corruption%'
OR [Text] LIKE '%backup%'
OR [Text] LIKE '%login fail%'
OR [Text] LIKE '%blocking%'
OR [Text] LIKE '%autogrow%'
OR [Text] LIKE '%availability%'
OR [Text] LIKE '%tempdb%'
OR [Text] LIKE '%disk%'
OR [Text] LIKE '%space%'
OR [Text] LIKE '%failed%'
OR [Text] LIKE '%suspect%'
OR [Text] LIKE '%restore%'
OR [Text] LIKE '%mirroring%'
);
-- Summary Report by Category
SELECT
'ERROR SUMMARY BY CATEGORY (Last 7 Days)' as ReportType,
ServerName,
ErrorCategory,
COUNT(*) as ErrorCount,
MAX(LogDate) as LatestOccurrence
FROM #ErrorAnalysis
WHERE ErrorCategory NOT IN ('Information')
GROUP BY ServerName, ErrorCategory
ORDER BY COUNT(*) DESC;
-- Critical Errors in Last 24 Hours
SELECT
'CRITICAL ERRORS (Last 24 Hours)' as ReportType,
ServerName,
LogDate,
ErrorCategory,
LEFT(ErrorMessage, 100) + CASE WHEN LEN(ErrorMessage) > 100 THEN '...' ELSE '' END as ErrorSummary
FROM #ErrorAnalysis
WHERE ErrorCategory IN ('Data Corruption', 'Memory Pressure', 'Disk Space Issues', 'Database Suspect', 'Backup Failures', 'Transaction Log Full')
AND LogDate >= DATEADD(hour, -24, GETDATE())
AND ErrorLevel >= 15
ORDER BY LogDate DESC;
-- High Frequency Issues
SELECT
'HIGH FREQUENCY ISSUES (>5 occurrences)' as ReportType,
ServerName,
ErrorCategory,
COUNT(*) as Occurrences,
MIN(LogDate) as FirstSeen,
MAX(LogDate) as LastSeen
FROM #ErrorAnalysis
WHERE ErrorCategory NOT IN ('Information', 'Warnings', 'Database Autogrow')
AND LogDate >= DATEADD(day, -7, GETDATE())
GROUP BY ServerName, ErrorCategory
HAVING COUNT(*) > 5
ORDER BY COUNT(*) DESC;
-- Performance Issues
SELECT
'PERFORMANCE ISSUES' as ReportType,
ServerName,
ErrorCategory,
COUNT(*) as Occurrences,
MIN(LogDate) as FirstSeen,
MAX(LogDate) as LastSeen
FROM #ErrorAnalysis
WHERE ErrorCategory IN ('Database Autogrow', 'TempDB Issues', 'Blocking Issues', 'Query Timeout', 'Deadlock')
AND LogDate >= DATEADD(day, -7, GETDATE())
GROUP BY ServerName, ErrorCategory
HAVING COUNT(*) >= 1
ORDER BY COUNT(*) DESC;
-- Login Failure Summary
SELECT
'LOGIN FAILURES (Last 24 Hours)' as ReportType,
ServerName,
COUNT(*) as FailureCount,
MIN(LogDate) as FirstFailure,
MAX(LogDate) as LastFailure
FROM #ErrorAnalysis
WHERE ErrorCategory = 'Login Failures'
AND LogDate >= DATEADD(day, -1, GETDATE())
GROUP BY ServerName
HAVING COUNT(*) > 10 -- Only show if more than 10 failures
ORDER BY COUNT(*) DESC;
-- Cleanup
DROP TABLE #ErrorLogRaw;
DROP TABLE #ErrorAnalysis;
dbWonderKid 