Pulling data from the error log

6

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

6 thoughts on “Pulling data from the error log

  1. Tom Hoirner says:

    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)

      • Tom Hoirner says:

        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.

  2. Carm Vecchio says:

    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!

    Carm Vecchio

    /* 
    Search-ErrorLog 
    - SQL2005 and greater 
    - reads all SQL ERRORLOGS on an instance 
      using a @search_text filter 
      and returns all rows with a matching string 
    c vecchio - csc 
    */ 
    set nocount on 
    -- declare vars 
    declare @max int, @i int, @search_text sysname; 
    
    -- set the string to search for 
    set @search_text = 'reconf'; 
    
    -- create tables 
    create table #logs (num int, [date] datetime, size int) 
    create table #logtext ([num] int null 
                         , [date] datetime 
                         , process nvarchar(255) 
                         , [text] nvarchar(max) ); 
    -- get the count of logs 
    insert into #logs exec xp_enumerrorlogs 
    -- get the number of errorlogs and initialize counter 
    select @max = max(num) , @i = 0 from #logs 
    -- read all the error logs looking for some text string 
    while @i &lt;= @max begin 
            insert into #logtext ([date], [process], [text]) 
            exec sp_readerrorlog @i, 1 , @search_text; 
            update #logtext set num = @i where num is null; 
            set @i = @i + 1;  what
    end 
    -- show results 
    select * from #logtext 
    -- delete the temp tables 
    drop table #logs 
    drop table #logtext

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,664 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: