Performance - Search SQL Server Error Log Files

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Unknown
Date:        16 May 2009

Description

 To search through all the SQL Error log files from TSQL.
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)

Code

Stored Procedure:


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



Test Code:


EXEC SearchSQLErrorLogs 'error'
EXEC SearchSQLErrorLogs 'check', NULL, NULL, 0, 1

ċ
usp_SearchSQLErrorLogs.sql
(4k)
Andy Hughes,
Aug 3, 2012, 11:38 AM
Comments