Search SQL Server Error Log Files

DROP PROCEDURE dbo.usp_SearchSQLErrorLogs

GO

CREATE PROCEDURE dbo.usp_SearchSQLErrorLogs

@SearchString NVARCHAR(255) = '',

@StartDate DATETIME = NULL,

@EndDate DATETIME = NULL,

@IgnoreBackups BIT = 1,

@CurrentLogOnly BIT = 0

AS

/***************************************************************

Purpose: To search through all the SQL Error log files.

The parameters are as follows:

@SearchString - Optional text string that will be used as a wildcard match

@StartDate - Optional search start date. Defaults to start of log

@EndDate - Optional search end date. Defaults to end of log

@IgnoreBackups - Optionally strip out all backup commands from results. Defaults to 1

@CurrentLogOnly BIT - Optionally restrict the search to current log file. Defaults to 0 (all log files)

Author: Unknown

History: 16 May 2009

****************************************************************/

BEGIN

SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#Log', 'U') > 0

DROP TABLE #Log;

IF OBJECT_ID('tempdb..#Logs', 'U') > 0

DROP TABLE #Logs;

DECLARE

@FirstLog INT,

@LastLog INT,

@CurrentLog INT;

-- initialise variables

IF @StartDate IS NULL

SET @FirstLog = 6;

IF @EndDate IS NULL

SET @LastLog = 0;

SELECT

@StartDate = COALESCE(@StartDate, '19000101'),

@EndDate = COALESCE(@EndDate, GETUTCDATE()),

@SearchString = '%' + COALESCE(@SearchString, '') + '%';

-- if we need to consider all logs, then create a table to hold the list

IF @CurrentLogOnly = 0 OR COALESCE(@FirstLog, @LastLog) IS NULL

BEGIN

CREATE TABLE #logs

(

ArchiveNo TINYINT,

dt DATETIME,

sz BIGINT

);

-- load the list of logs

INSERT #logs

EXEC [master].dbo.sp_enumerrorlogs @p1 = 1;

-- work out the start and end dates for each log and use this

-- to extract the log numbers of the first and last logs

SELECT

@FirstLog = MAX(ArchiveNo),

@LastLog = MIN(ArchiveNo)

FROM

(

SELECT

ArchiveNo,

[Start] = COALESCE(

(

SELECT dt

FROM #Logs

WHERE ArchiveNo = L.ArchiveNo + 1

), '19000101'),

[End] = dt

FROM #Logs AS L

) AS x

WHERE [Start] BETWEEN @StartDate AND @EndDate

OR [End] BETWEEN @StartDate AND @EndDate;

IF OBJECT_ID('tempdb..#Logs', 'U') > 0

DROP TABLE #Logs;

END

IF @CurrentLogOnly = 1

SELECT

@FirstLog = 0,

@LastLog = 0;

-- create a temp table to hold the log details

CREATE TABLE #Log

(

LogDate DATETIME,

ProcessInfo VARCHAR(64),

MessageText NVARCHAR(MAX)

);

CREATE CLUSTERED INDEX d ON #Log(LogDate);

SELECT @CurrentLog = @FirstLog;

-- now we iterate through the log files, reading them in to the temp table

WHILE @CurrentLog >= @LastLog

BEGIN

INSERT #Log

EXEC [master].dbo.sp_readerrorlog

@p1 = @CurrentLog;

SET @CurrentLog = @CurrentLog - 1;

END

-- if we are to ignore backu commands, delete them from the temp table

IF @IgnoreBackups = 1

BEGIN

DELETE #Log

WHERE ProcessInfo = 'Backup'

AND ( MessageText LIKE 'Database backed up%'

OR

MessageText LIKE 'Log backed up%'

);

END

-- finally, select only the data that matches the search string.

SELECT LogDate, MessageText

FROM #Log

WHERE LogDate BETWEEN @StartDate AND @EndDate

AND MessageText LIKE @SearchString

ORDER BY LogDate;

IF OBJECT_ID('tempdb..#Log', 'U') > 0

DROP TABLE #Log;

END

GO