Error Log Analysis Script
SQL Server Error Logs show various aspects regarding the state of the SQL Server Instance. Error log analysis helps ensure nothing unexpected happens, and is useful for diagnosing existing problems. The error log is stored in text files on the filesystem, and is visible using SQL Server Management Studio through the Object Explorer. To see the logs, click the “Management” node in the object explorer. Then open the “SQL Server Logs” node to see the log files. The Object Explorer looks like this when you drill-down to the SQL Server Logs node:
SQL Server Error Logs
Manually searching for a specific message in set of large error logs can be a time-consuming process. To mitigate that pain, I’ve created a script that automates gathering the content of the logs.
The script programmatically gathers the log entries into a temporary table. It then shows results in reverse-chronological order. “Noise” entries are filtered out of the resulting display, allowing you to easily focus on the important messages. Customize the list of “noise” messages to suit your needs, via the contents of the #exclusions table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 |
/* Shows the contents of the SQL Server Error Log files, up to a maximum of the most recent 9 files. Certain "noisy" log messages are filtered out via the #exclusions temp table. If you choose, modify the contents to suit your needs. */ SET NOCOUNT ON; DECLARE @ErrorLogType tinyint; SET @ErrorLogType = 1; --1 for SQL Server Error log, 2 for SQL Server Agent Log IF OBJECT_ID(N'tempdb..#exclusions', N'U') IS NOT NULL DROP TABLE #exclusions; CREATE TABLE #exclusions ( LogTextToExclude varchar(1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL PRIMARY KEY CLUSTERED , LogType tinyint NOT NULL ); -- the pipe symbol, "|", should be used as the escape character INSERT INTO #exclusions (LogTextToExclude, LogType) VALUES ('%Using ''dbghelp.dll'' version ''4.0.5''%', 1) , ('%informational message%no user action is required%', 1) , ('Log was backed up%', 1) , ('Database backed up%', 1) , ('BACKUP DATABASE %', 1) , ('DBCC %', 1) , ('Setting database option RECOVERY to %', 1) , ('This instance of SQL Server has been using a process ID of %', 1) , ('Starting up database%', 1) , ('Software Usage Metrics is enabled.', 1) , ('|[184|] Job completion for % is being logged to sysjobhistory%', 2) , ('|[177|] Job % has been requested to run by Schedule %', 2) , ('|[248|] Saving NextRunDate/Times for all updated job schedules...%', 2) , ('|[249|] % job schedule|(s|) saved%', 2) , ('|[182|] Job completion for % is being logged to the eventlog%', 2) , ('|[171|] There are % alert|(s|) in the alert cache%', 2) , ('|[168|] There are 21 job(s) |[0 disabled|] in the job cache%', 2) , ('|[170|] Populating alert cache...%', 2) , ('|[473|] Database Mail profile DBA refreshed.%', 2) , ('|[353|] Mail session started%', 2) , ('|[273|] Mail dispatcher started%', 2) , ('|[174|] Job scheduler engine started |(maximum user worker threads: 200, maximum system worker threads: 100|)%', 2) , ('|[193|] Alert engine started |(using Eventlog Events|)%', 2) , ('|[146|] Request servicer engine started%', 2) , ('|[167|] Populating job cache...%', 2) , ('|[133|] Support engine started%', 2) , ('|[271|] Idle processor poller started%', 2) IF OBJECT_ID(N'tempdb..#mverrlog', N'U') IS NOT NULL DROP TABLE #mverrlog; CREATE TABLE #mverrlog ( RowNum int PRIMARY KEY CLUSTERED IDENTITY(1,1) , ErrorLogFileNum int NULL , LogDate datetime , ProcessInfo varchar(255) , [Text] varchar(4000) ); DECLARE @ErrorLogCount int; DECLARE @cmd varchar(2000); DECLARE @msg varchar(1000); DECLARE @ErrorlogEnum TABLE ( [Archive #] varchar(3) NOT NULL PRIMARY KEY CLUSTERED , [Date] datetime NOT NULL , [Log File Size (Byte)] int NOT NULL ); INSERT INTO @ErrorlogEnum ([Archive #], [Date], [Log File Size (Byte)]) EXEC sys.sp_enumerrorlogs; SET @ErrorLogCount = COALESCE(( SELECT COUNT(1) FROM @ErrorlogEnum eln WHERE eln.[Log File Size (Byte)] > 0 ), 0); IF @ErrorLogCount > 9 SET @ErrorLogCount = 9; DECLARE @FileNum INT; SET @FileNum = 0 WHILE @FileNum < @ErrorLogCount BEGIN SET @msg = 'Retreiving log ' + CONVERT(varchar(100), @FileNum); RAISERROR (@msg, 0, 1) WITH NOWAIT; --send progress to the "Messages" tab INSERT INTO #mverrlog (LogDate, ProcessInfo, Text) EXEC sys.xp_readerrorlog @FileNum, @ErrorLogType; UPDATE #mverrlog SET ErrorLogFileNum = @FileNum WHERE ErrorLogFileNum IS NULL; SET @FileNum = @FileNum + 1; END SELECT FileNumber = el.ErrorLogFileNum , el.LogDate , el.Text FROM #mverrlog el WHERE NOT EXISTS ( SELECT 1 FROM #exclusions e WHERE (el.Text LIKE e.LogTextToExclude ESCAPE '|') AND e.LogType = @ErrorLogType ) ORDER BY el.ErrorLogFileNum, el.RowNum DESC;</code> |
The first column shows which error log the message resides in. 0 indicates the current error log, with higher numbers representing older log files. The code currently limits the number of log files inspected to 9. Modify that in the code if you need to look at more than the 9 most recent log files.
Once you run the code above, you can run any query you like against the #mverrlog temporary table. Perhaps so you can see a specific date-range, or messages concerning a specific database.
Let me know if you found this code useful; and check out the rest of our SQL Server Tools.
Microsoft’s Error Log documentation is a bit sparse!
Looks good – but I get this
Warning! The maximum key length is 900 bytes. The index ‘PK__#exclusi__3E0094632C52F5D4’ has maximum length of 1000 bytes. For some combination of large values, the insert/update operation will fail.
Retreiving log 0
Retreiving log 1
Retreiving log 2
Retreiving log 3
Retreiving log 4
Retreiving log 5
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Thanks for the reply! I’ve experienced that error myself on occasion – I believe it means xp_readerrorlog tried to read an empty error log file. If you modify line 87 in the code to
IF @ErrorLogCount > 5 SET @ErrorLogCount = 5;you’ll be able to get past the error. Once I have some spare time, I’ll look into mitigating this problem directly in the code.