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

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:

• Unable to recycle Errorlog – sp_cycle_errorlog – OS error 1392
• How to Write Errors in Error Log? – Interview Question of the Week #175
• Too Many SQLDump Files Consuming a Lot of Disk Space. What Should You Do?

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