Introduction
The stored procedure
xp_readerrorlog (and also
sp_readerrorlog) allows searching
of the SQL Server error logs. However only
one log can be searched at a time using these procedures. This script searches all available logs, by looping through each log in turn.
Script
The script below will search either the SQL Server log or SQL Server Agent log, and 1 or 2 search conditions can be entered. The script could be extended
to include the other parameters available in
xp_readerrorlog such as filtering on dates.
-- Search through all available logs in one go
DECLARE @SearchString1 NVARCHAR(4000)
DECLARE @SearchString2 NVARCHAR(4000)
DECLARE @LogType INT
-- ------------------------------------------------------------------------------
-- User configurable settings - set up the search conditions here.
-- First search string (or leave blank for everything)
SET @SearchString1 = ''
-- Second search string (or leave blank for everything)
SET @SearchString2 = ''
-- Set log to be searched - 1=SQL Server log, 2=SQL Agent log
SET @LogType = 1
-- ------------------------------------------------------------------------------
-- Generate a list of all logs, and store in a temporary table.
CREATE TABLE #ListOfLogs (LogNumber INT, StartDate DATETIME, SizeInBytes INT)
INSERT INTO #ListOfLogs EXEC xp_enumerrorlogs @LogType
-- Iterate around all the logs gathering results
CREATE TABLE #Results (LogDate DATETIME,ProcessInfo NVARCHAR(4000),Test NVARCHAR(4000))
DECLARE @Count INT
SET @Count = 0
WHILE @Count <= (SELECT MAX(LogNumber) FROM #ListOfLogs)
BEGIN
INSERT INTO #Results EXEC xp_readerrorlog
@Count
,@LogType -- 1=SQL Server log, 2=SQL Agent log
,@SearchString1 -- Search string
,@SearchString2 -- 2nd search string
SET @Count = @Count + 1
END
-- Return the results from the temporary table.
SELECT * FROM #Results ORDER BY LogDate DESC
-- Tidy up.
DROP TABLE #ListOfLogs
DROP TABLE #Results
By default SQL Server retains 6 log files, but this can be increased up to 99 using the GUI, with a new log being started each time SQL Server is
started. Log files can become huge and it can take a long time to load them into the SQL Server Management Studio GUI, so using SQL to search can be
a lot faster.
I should say at this point that both
xp_readerrorlog and
xp_enumerrorlogs are undocumented so could be removed by Microsoft without notice, so best to
avoid using them for production code. However they are widely used, indeed SQL Server Management Studio uses them to gather log information.