Pulling data from the error log8
August 17, 2015 by Kenneth Fisher
The other day I was asked to pull a list of errors from the SQL Server log into a table for analysis. It seemed like something that others might find useful so I decided to post what I came up with.
First I had to get the number of error log files. It turned out to be a bit trickier than I expected, so rather than spend a lot of time on it I searched online and found a script here that did just what I needed. To give proper credit I’ve put some comments in the code around that part of it.
A couple of notes on what’s going on in the script: I’m using sys.xp_readerrorlog to read the log. This xp is not documented so you can’t be certain that it will always do what you expect. But it is fairly heavily used so it’s probably reasonably safe. I’m loading the data file by file into a temp table then loading that data into a final storage table. This means that I can run this query over and over again and only load new data into the final table.
The idea here was to be able to run this across dozens of instances then collect the data into one place for processing. In our case we were seeing some IO issues and needed to isolate them to specific drives on specific instances. I’m intending the script to be a model and it should be easy enough to modify it to read other types of errors or warnings as they fit your situation.
USE Test GO IF OBJECT_ID('IOErrorLog') IS NULL CREATE TABLE IOErrorLog ( ServerName nvarchar(256), LogFileName nvarchar(4000), Occurances int, LogDate datetime, ProcessInfo nvarchar(50), ErrorText nvarchar(max), CONSTRAINT pk_IOErrorLog PRIMARY KEY (ServerName, LogDate, LogFileName) ) ---------------------------------------------------------------------- -- This part of the code was found here: -- https://ask.sqlservercentral.com/questions/99484/number-of-error-log-files.html DECLARE @FileList AS TABLE ( subdirectory NVARCHAR(4000) NOT NULL ,DEPTH BIGINT NOT NULL ,[FILE] BIGINT NOT NULL ); DECLARE @ErrorLog NVARCHAR(4000), @ErrorLogPath NVARCHAR(4000); SELECT @ErrorLog = CAST(SERVERPROPERTY(N'errorlogfilename') AS NVARCHAR(4000)); SELECT @ErrorLogPath = SUBSTRING(@ErrorLog, 1, LEN(@ErrorLog) - CHARINDEX(N'\', REVERSE(@ErrorLog))) + N'\'; INSERT INTO @FileList EXEC xp_dirtree @ErrorLogPath, 0, 1; DECLARE @NumberOfLogfiles INT; SET @NumberOfLogfiles = (SELECT COUNT(*) FROM @FileList WHERE [@FileList].subdirectory LIKE N'ERRORLOG%'); -- SELECT @NumberOfLogfiles; ---------------------------------------------------------------------- CREATE TABLE #temp ( LogDate datetime, ProcessInfo nvarchar(500), ErrorText nvarchar(max)) DECLARE @p1 INT = 0 WHILE @p1 < @NumberOfLogfiles BEGIN -- P1 is the file number starting at 0 DECLARE @p2 INT = 1, -- P2 1 for SQL logs, 2 for SQL Agent logs @p3 NVARCHAR(255) = 'occurrence', -- P3 is a value to search on @p4 NVARCHAR(255) = 'i/o' -- P4 is another search value BEGIN TRY INSERT INTO #temp EXEC sys.xp_readerrorlog @p1,@p2,@p3,@p4 END TRY BEGIN CATCH PRINT 'Error occurred processing file ' + cast(@p1 as varchar(10)) END CATCH SET @p1 = @p1 + 1 END INSERT INTO IOErrorLog SELECT DISTINCT @@SERVERNAME, SUBSTRING(ErrorText,charindex('[',ErrorText)+1, charindex(']',ErrorText)-charindex('[',ErrorText)-1), SUBSTRING(ErrorText, 27, patindex('%occurrence%',ErrorText)-28), LogDate, ProcessInfo, ErrorText FROM #temp WHERE #temp.LogDate NOT IN ( SELECT LogDate FROM IOErrorLog WHERE ServerName = @@SERVERNAME) DROP TABLE #temp SELECT * FROM IOErrorLog
Category: Microsoft SQL Server, SQLServerPedia Syndication, System Functions and Stored Procedures, T-SQL | Tags: microsoft sql server, problem resolution, system functions, T-SQL
8 thoughts on “Pulling data from the error log”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Getting syntax error on the substring commands. Using 2008R2. Is this good for this SQL Server version?
The substrings in the insert query I assume? Did you change the search parameters? (@P3 & @P4)
Yes, changed to ‘Log’ and ‘was” respectively (I was testing SP and using these as quick parameter inputs). Also changed substring within the insert to SUBSTRING(ErrorText, 27, patindex(‘%Log%’,ErrorText)-28). I get Msg 537, Level 16, State 5, Line 63
Invalid length parameter passed to the LEFT or SUBSTRING function.
The substring columns I have in that query are specific to the error I was pulling. I wouldn’t expect those particular substrings won’t work except on that error.
I would recommend looking at the #temp table, review the error itself and see if there is any information in there that you need to pull out seperatly. If not then just delete the substring columns completely, and if so then modify them appropriately.
Ken, nice post. Is there some reason you chose not to use xp_enumerrorlogs (or sp_) besides they are undocumented? Sure saves a lot of code. Here’s what I use. Your mileage may vary. Keep up the good blog posts!
Nice version. There are a million ways to skin a cat 🙂
[…] It pulls all of the data from the error log and dumps it into a temp table. I did one version here but it was pretty specific to I/O errors. I also gave some homework to find/build a script like it. […]
[…] Based on some code here I put together the following script: […]