Searching ALL SQL Server Logs using TSQL

Category : Scripts Published : June 1, 2016 User Rating : 5 Stars      Views : 3 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.

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.
Link back to this article : https://www.sqlmatters.com/Articles/Searching-ALL-SQL-Server-Logs-using-TSQL.aspx

Keywords

SQL 2012, 2008R2, 2014, SQL Agent, Log, TSQL


Comments

Post a comment   No login required !

Name : Email : Website :
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered