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