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

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, System Functions and Stored Procedures, T-SQL Tagged: microsoft sql server, problem resolution, system functions, T-SQL