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

   DECLARE @ErrorLogCount INT 
   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

      INSERT INTO @ErrorLogInfo
      EXEC sp_readerrorlog @ErrorLogCount

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

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

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: Logo

You are commenting using your 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