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

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: