Archive
Archive for April 13, 2012
Reading the xp_readerrorlog Using a WHILE Loop
April 13, 2012
3 comments
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 GO -- 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 BEGIN -- 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 END -- 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
Categories: SQL Server, SQL Server Agent, T-SQL
SQL Server, SQL Server Agent, T-SQL