SQL SERVER – T-SQL Script: How to Search for Multiple Values in ERRORLOG?

SQL SERVER - T-SQL Script: How to Search for Multiple Values in ERRORLOG? computer There are many situations where we need to apply filters in ERRORLOG to look at meaningful data. In this blog, we would learn how to search for multiple values in the SQL Server ERRORLOG file.

If you are new to SQL Server and don’t know what is ERRORLOG then you should open it once and see it. Refer my earlier blog to find the location of this important file. SQL SERVER – Where is ERRORLOG? Various Ways to Find ERRORLOG Location

I always say the that ERRORLOG name is misleading as you would find tons of “information” messages also.

Let’s say you need to search for multiple texts at the same time in the file, how would you do it? For example, I would like to know if there was any corruption error reported in ERRORLOG recently.

WORKAROUND/SOLUTION

You can use below sample code and modify where clause based on your requirement.

SET NOCOUNT ON
GO
CREATE TABLE #ERR_CheckCorruptionLog (
	LogDate DATETIME
	,ProcessInfo VARCHAR(256)
	,MessageText VARCHAR(max)
	)
GO
INSERT #ERR_CheckCorruptionLog
EXEC sp_readerrorlog
GO
SELECT e.LogDate
	,e.ProcessInfo
	,e.MessageText
FROM #ERR_CheckCorruptionLog e
WHERE (
		e.MessageText LIKE 'DBCC CHECKTABLE%'
		AND e.MessageText LIKE 'DBCC CHECKDB%'
		AND e.MessageText LIKE '%found%'
		AND e.MessageText LIKE '%errors%'
		AND e.MessageText NOT LIKE '%found 0 errors%'
		)
DROP TABLE #ERR_CheckCorruptionLog

Do you have any other script to monitor ERRORLOG?

Here is few related blog post on the same subject:

Reference: Pinal Dave (https://blog.SQLAuthority.com)

SQL Error Messages, SQL Scripts, SQL Server
Previous Post
SQL SERVER – How to Check If Instant File Initialization Enabled or Not?
Next Post
SQL SERVER – How to Generate Random Password? – Enhanced Version

Related Posts

1 Comment. Leave new

  • Hi Pinal,

    I think you missed out % in the beginning for one of the below conditions given in the example.

    e.MessageText LIKE ‘DBCC CHECKTABLE%’
    AND e.MessageText LIKE ‘DBCC CHECKDB%’

    Can you please correct me if I am wrong?. As per my understanding, the column MessageText column either would find a word starting with either “DBCC CHECKTABLE” or “DBCC CHECKDB”, but not both.

    Reply

Leave a Reply