text: /*
  Created: 2019-03-05 by Max Vernon
  Modified: 2019-03-26 by Konstantin Taranov
  Original link: https://www.sqlserverscience.com/tools/error-log-analysis-script/
  Source link: https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/Read_Error_Log_File.sql
  Note: Shows the contents of the SQL Server Error Log files, up to a maximum of the most recent 9 files.
  Certain "noisy" log messages are filtered out via the #exclusions temp table.
  */
  
  SET NOCOUNT ON;
  
  DECLARE @ErrorLogType tinyint;
  SET @ErrorLogType = 1; --1 for SQL Server Error log, 2 for SQL Server Agent Log
  
  IF OBJECT_ID(N'tempdb..#exclusions', N'U') IS NOT NULL DROP TABLE #exclusions;
  
  CREATE TABLE #exclusions
  (
      LogTextToExclude VARCHAR(4000)-- COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
         -- PRIMARY KEY CLUSTERED
      , LogType tinyint NOT NULL
  );
  
  -- the pipe symbol, "|", should be used as the escape character
  INSERT INTO #exclusions (LogTextToExclude, LogType)
  VALUES ('%Using ''dbghelp.dll'' version ''4.0.5''%', 1)
      , ('%informational message%no user action is required%', 1)
      , ('Log was backed up%', 1)
      , ('Database backed up%', 1)
      , ('BACKUP DATABASE %', 1)
      , ('DBCC %', 1)
      , ('Setting database option RECOVERY to %', 1)
      , ('This instance of SQL Server has been using a process ID of %', 1)
      , ('Starting up database%', 1)
      , ('Software Usage Metrics is enabled.', 1)
      , ('|[184|] Job completion for % is being logged to sysjobhistory%', 2)
      , ('|[177|] Job % has been requested to run by Schedule %', 2)
      , ('|[248|] Saving NextRunDate/Times for all updated job schedules...%', 2)
      , ('|[249|] % job schedule|(s|) saved%', 2)
      , ('|[182|] Job completion for % is being logged to the eventlog%', 2)
      , ('|[171|] There are % alert|(s|) in the alert cache%', 2)
      , ('|[168|] There are 21 job(s) |[0 disabled|] in the job cache%', 2)
      , ('|[170|] Populating alert cache...%', 2)
      , ('|[473|] Database Mail profile DBA refreshed.%', 2)
      , ('|[353|] Mail session started%', 2)
      , ('|[273|] Mail dispatcher started%', 2)
      , ('|[174|] Job scheduler engine started |(maximum user worker threads: 200, maximum system worker threads: 100|)%', 2)
      , ('|[193|] Alert engine started |(using Eventlog Events|)%', 2)
      , ('|[146|] Request servicer engine started%', 2)
      , ('|[167|] Populating job cache...%', 2)
      , ('|[133|] Support engine started%', 2)
      , ('|[271|] Idle processor poller started%', 2)
  
  
  IF OBJECT_ID(N'tempdb..#mverrlog', N'U') IS NOT NULL
  DROP TABLE #mverrlog;
  CREATE TABLE #mverrlog
  (
        RowNum INT PRIMARY KEY CLUSTERED IDENTITY(1,1)
      , ErrorLogFileNum INT NULL
      , LogDate DATETIME NOT NULL
      , ProcessInfo NVARCHAR(255) NOT NULL
      , [Text]      NVARCHAR(4000) NOT NULL
  );
  
  DECLARE @ErrorLogCount int;
  DECLARE @cmd varchar(2000);
  DECLARE @msg varchar(1000);
  
  DECLARE @ErrorlogEnum TABLE(
        [Archive #] varchar(3) NOT NULL PRIMARY KEY CLUSTERED
      , [Date] datetime NOT NULL
      , [Log File Size (Byte)] int NOT NULL
  );
  
  INSERT INTO @ErrorlogEnum ([Archive #], [Date], [Log File Size (Byte)])
  EXEC sys.sp_enumerrorlogs;
  
  SET @ErrorLogCount = COALESCE((
      SELECT COUNT(1)
      FROM @ErrorlogEnum eln
      WHERE eln.[Log File Size (Byte)] > 0
      ), 0);
  
  IF @ErrorLogCount > 9 SET @ErrorLogCount = 9;
  
  DECLARE @FileNum INT;
  SET @FileNum = 0
  
  WHILE @FileNum < @ErrorLogCount
  BEGIN TRY
      SET @msg = 'Retreiving log ' + CONVERT(varchar(100), @FileNum);
      RAISERROR (@msg, 0, 1) WITH NOWAIT; --send progress to the "Messages" tab
  
      INSERT INTO #mverrlog (LogDate, ProcessInfo, [Text])
      EXEC sys.xp_readerrorlog @FileNum, @ErrorLogType;
  
      UPDATE #mverrlog
      SET ErrorLogFileNum = @FileNum
      WHERE ErrorLogFileNum IS NULL;
  
      SET @FileNum = @FileNum + 1;
  END TRY
  BEGIN CATCH
      PRINT 'Error: '       + CONVERT(varchar(50), ERROR_NUMBER())  +
            ', Severity: '  + CONVERT(varchar(5), ERROR_SEVERITY()) +
            ', State: '     + CONVERT(varchar(5), ERROR_STATE())    +
            ', Procedure: ' + ISNULL(ERROR_PROCEDURE(), '-')        +
            ', Line: '      + CONVERT(varchar(5), ERROR_LINE())     +
            ', User name: ' + CONVERT(sysname, ORIGINAL_LOGIN());
      PRINT(ERROR_MESSAGE());
      PRINT('Change @FileNum to smaller value!');
  END CATCH
  
  SELECT FileNumber = el.ErrorLogFileNum
      , el.LogDate
      , el.Text
  FROM #mverrlog el
  WHERE NOT EXISTS (
      SELECT 1
      FROM #exclusions e
      WHERE (el.Text LIKE e.LogTextToExclude ESCAPE '|')
          AND e.LogType = @ErrorLogType
      )
  ORDER BY el.ErrorLogFileNum, el.RowNum DESC;
  