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) = '')
AS
BEGIN
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 DESC
END
Use it as follows:
Exec SearchLogFiles 1,'ERROR'