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 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
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
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
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:
https://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql/
Thanks,
Frank