Script to find something in SQL Server logs

It is always difficult to find some relevant information about any error or record from SQL Server logs mostly when you have 100 log files and also has backups information in the logs.

Use below script to find a specific string looping through all your logs.

--Query to search the Logs for a specific string in the error logs
SET NOCOUNT ON

   DECLARE @ErrorLogCount INT 
   DECLARE @LastLogDate DATETIME
   DECLARE @StringtoSearch NVARCHAR(100) = '%Sample%' --Change Sample to the string that you want to look

   DECLARE @ErrorLogInfo TABLE (
       LogDate DATETIME
      ,ProcessInfo NVARCHAR (50)
      ,[Text] NVARCHAR (MAX)
      )
   
   DECLARE @EnumErrorLogs TABLE (
       [Archive#] INT
      ,[Date] DATETIME
      ,LogFileSizeMB INT
      )

   INSERT INTO @EnumErrorLogs
   EXEC sp_enumerrorlogs

   SELECT @ErrorLogCount = MIN([Archive#]), @LastLogDate = MAX([Date])
   FROM @EnumErrorLogs

   WHILE @ErrorLogCount IS NOT NULL
   BEGIN

      INSERT INTO @ErrorLogInfo
      EXEC sp_readerrorlog @ErrorLogCount

      SELECT @ErrorLogCount = MIN([Archive#]), @LastLogDate = MAX([Date])
      FROM @EnumErrorLogs
      WHERE [Archive#] > @ErrorLogCount
  
   END

   SELECT LogDate, ProcessInfo, [Text] FROM @ErrorLogInfo WHERE [Text] LIKE @StringtoSearch
   SET NOCOUNT OFF

GitHub link.

It will create a temporary table and will load all the logs into it, so if you have bigger log files it might not be a solution for you.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s