You can use a script similar to the following to read through all SQL Server Log files:
CREATE PROCEDURE SearchLogFiles (@LogType INT = 1, Filter NVARCHAR(MAX) = '')ASBEGIN DECLARE @LogsTable TABLE (LogIndex INT, LogDate DATETIME, LogSize INT) DECLARE @LogRows TABLE (LogDate DATETIME, ProcessInfo NVARCHAR (4000), Test NVARCHAR (4000)) INSERT INTO @LogsTable EXEC xp_enumerrorlogs @LogType DECLARE @index INT = 0 WHILE @index <= (SELECT MAX (LogIndex) FROM @LogsTable) BEGIN INSERT INTO @LogRows EXEC xp_readerrorlog @index , @LogType -- 1=SQL Server log, 2=SQL Agent log , @filterstr -- filter string , @filterstr SET @index += 1 END SELECT * FROM @LogRows ORDER BY LogDate DESCEND
Use it as follows:
Exec SearchLogFiles 1,'ERROR'