Home > SQL Server, SQL Server Agent, T-SQL > Reading the xp_readerrorlog Using a WHILE Loop

Reading the xp_readerrorlog Using a WHILE Loop

Last week, I needed to search through all of the SQL Server error logs on an instance. I have used xp_readerrorlog before, using the information found here. We retain 99 log files, which is the maximum number of files that can be kept. I needed to search through all of these files, so I put together the following script. This is another example of using a WHILE loop in gathering information from SQL Server.

USE master

-- Create temp table to hold the output of the error logs

CREATE TABLE #errorlog
(errdate DATETIME
,errproc SYSNAME
,errtext VARCHAR(4000))

-- Declare and initialize the @loopcount variables

DECLARE @loopcount INT
SET @loopcount = 1

-- Loop through the error logs
-- We keep the 99 logs, which the maximum
-- Change the loop limit accordingly

WHILE @loopcount <= 99

	-- Insert the results of xp_readerrorlog into the temp table
	INSERT INTO #errorlog
	-- You can find the parameters for xp_readerrorlog at the following link
	-- http://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql/
	-- The parms I use below are as follows:
	-- @loopcount - file number, with 0 being the current file
	-- 1 - indicates the SQL error log - a 2 would query the SQL Agent log
	-- 'DELETE' - a search argument
	EXEC xp_readerrorlog @loopcount, 1, 'DELETE'
	-- Increment your loop counter
	SET @loopcount = @loopcount + 1

-- Select the results of your loop
-- Alternately, you can dump the entire log file and then use WHERE predicates to 
-- select what you are interested in

SELECT * FROM #errorlog

-- Clean up your temp table
DROP TABLE #errorlog

  1. April 23, 2013 at 07:01

    You actually make it seem really easy together with your presentation but I find this matter to be really one thing that I feel I might never understand. It kind of feels too complicated and extremely vast for me. I’m looking ahead to your next publish, I will try to get the hang of it

  2. Jim
    November 7, 2016 at 09:29

    Why would you want to read all 99 errorlogs? Some of them could be months old and I have never had a reason to go that far back. I’m looking for something to scan the current errorlog that can discern a potential problem. I have a list of entries that are expected and OK to have. I want to find the entries that are NOT OK

    • November 6, 2017 at 13:54

      Jim, apologies for the delayed reply. To search the current log, you can execute the following code:

      EXEC xp_readerrorlog 0, 1, N’Your search string’;

      Optionally, you can add a second search argument:

      EXEC xp_readerrorlog 0, 1, N’Your search string’, N’Your second search string’;

      You can also provide a start and end time and sort order. All parameters can be found here:



  1. No trackbacks yet.

Leave a Reply

%d bloggers like this: