Script to consolidate SQL Server agent logs

SQL Server, SQL Server Agent, T-SQL / By / August 29, 2022

Problem:
How can I view all SQL Server agent logs at-one-time without querying each individual log on its own?

Solution:
The answer is to put this data into a single table allowing you to better query this information based on your individual needs. I’ve given a possible solution with the following T-SQL script.

/*
Author: Brent Sodtke
Created: 2022-08
Description: Consolidates all SQL Server Agent logs for easier analysis.
License: MIT
PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED
*/

-- create temporary table to store results of xp_enumerrorlogs below
IF OBJECT_ID(N'tempdb..#EnumAgentLogs') is not null
   DROP TABLE tempdb..#EnumAgentLogs;
CREATE TABLE tempdb..#EnumAgentLogs(archiveno INT, logdate DATETIME, logsizebytes BIGINT);

-- declare variable to store the number of agent logs on the instance
DECLARE @NumberOfAgentLogs INT = 0;
-- use undocumented extended stored procedure to enumerate the agent logs
INSERT INTO #EnumAgentLogs EXECUTE master.sys.xp_enumerrorlogs 2; -- 1 = SQL error logs, 2 = SQL agent logs

SET @NumberOfAgentLogs = (SELECT COUNT(*) FROM #EnumAgentLogs);

-- create temporary table to store results of all agent logs
IF OBJECT_ID(N'tempdb..#AllAgentLogs') is not null
   DROP TABLE tempdb..#AllAgentLogs;
CREATE TABLE tempdb..#AllAgentLogs (logdate DATETIME, errorlevel SMALLINT, msg NVARCHAR(4000));

-- iterate through agent logs one-at-a-time and store contents in temporary table for later analysis.
DECLARE @x INT = 0;
WHILE (@x < @NumberOfAgentLogs) -- using less than here since the log archive number is zero based
BEGIN
   INSERT INTO #AllAgentLogs
   EXECUTE msdb.sys.xp_readerrorlog @x, 2
   SET @x = @x + 1
END;

SELECT * FROM #AllAgentLogs ORDER BY logdate DESC;

/*
-- run this block of code when you are done to destroy temporary tables used in this process.

IF OBJECT_ID(N'#EnumAgentLogs') is not null DROP TABLE #EnumAgentLogs;
IF OBJECT_ID(N'#AllAgentLogs') is not null DROP TABLE #AllAgentLogs;
*/

Let me take you through the script, and ultimately my thinking process, by breaking down the above into smaller chunks.

First, I create a temporary table to hold the contents of xp_enumerrorlogs. Since I have to figure out the number of logs to enumerate automatically and without error, using the extended stored procedure xp_enumerrorlogs is our best bet.

Since xp_enumerrorlogs is undocumented, I assumed and mapped the output to a SMALLINT, DATETIME, and BIGINT data types for the archive number, log date and log file size respectively.

-- create temporary table to store results of xp_enumerrorlogs below
IF OBJECT_ID(N'tempdb..#EnumAgentLogs') is not null
   DROP TABLE tempdb..#EnumAgentLogs;
CREATE TABLE tempdb..#EnumAgentLogs(archiveno INT, logdate DATETIME, logsizebytes BIGINT);

Second, I insert the result set of the xp_enumerrorlogs output to the temporary table I just created, #EnumAgentLogs. I do this so I can then count the number of rows returned from this extended stored procedure with each row corresponding to a log file at the OS level. Note that the number of logs could vary, and if you’ve been running this instance of SQL Server for a while, more likely than not, you will have at most 10 SQL Server agent error logs, the current log plus nine (9) archive logs.

-- declare variable to store the number of agent logs on the instance
DECLARE @NumberOfAgentLogs INT = 0;
-- use undocumented extended stored procedure to enumerate the agent logs
INSERT INTO #EnumAgentLogs EXECUTE master.sys.xp_enumerrorlogs 2; -- 1 = SQL error logs, 2 = SQL agent logs

SET @NumberOfAgentLogs = (SELECT COUNT(*) FROM #EnumAgentLogs);

 

Bonus: If you are curious, your current SQL instance agent error log and path can be determined by running the following in your T-SQL editor of choice. This might save some time if you are accustomed to manually trying to find the files via File Explorer, especially if you are running multiple instances… which you shouldn’t be! 🙂

EXECUTE master.sys.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'SOFTWARE\MICROSOFT\MSSQLSERVER\SQLSERVERAGENT','ErrorLogFile'

However, if the SQL agent service doesn’t get restarted frequently, someone has deleted or moved the SQLAGENT numbered files or this is a newly created instance, there could be less than 10 agent error logs. Therefore, I need to determine that number, I cannot assume the maximum of 10, otherwise I’ll get errors when executing the script.

Third, I create one temporary table for holding the contents of all of the individual logs. I’m calling this temporary table #AllAgentLogs.

-- create temporary table to store results of all agent logs
IF OBJECT_ID(N'tempdb..#AllAgentLogs') is not null
   DROP TABLE tempdb..#AllAgentLogs;
CREATE TABLE tempdb..#AllAgentLogs (logdate DATETIME, errorlevel SMALLINT, msg NVARCHAR(4000));

Fourth, the core of the script, a WHILE loop iterating through the logs and storing the contents of each into the temporary table created above, #AllAgentLogs. I start at 0 (the most current log) and work our way up to the number of agent error logs determined earlier in the script.

-- iterate through agent logs one-at-a-time and store contents in temporary table for later analysis.
DECLARE @x INT = 0;
WHILE (@x < @NumberOfAgentLogs) -- using less than here since the log archive number is zero based
BEGIN
   INSERT INTO #AllAgentLogs
   EXECUTE msdb.sys.xp_readerrorlog @x, 2
   SET @x = @x + 1
END;

I now have all of the SQL agent error logs compiled into a single readable temporary table. This is where I simply show all of the records ordering them by their log date descending. But this is where you can deviate or add to the script and do whatever you like with the data. Since the data is stored in a temporary table, the underlying data in the OS files will not be manipulated.

At this point, you can do much more with the log data than you normally would be able to with sp_readerrorlog, such as:

  • Create a user stored procedure using this script so it is easily referenced in the future.
  • Modify the script to use the search parameters of xp_readerrorlog.
  • Periodically run the script, storing the results in another database/table for later analysis.
  • Modify the script to reference the SQL Server instance error logs instead of the agent logs.

Caveats:
Please take note that on heavier used systems, logs are typically larger in size, therefore this script could take longer than expected importing them into the temporary table.

Did you find this useful? Do you have a better way to achieve the desired result using T-SQL? Let me know in the comments below. Cheers.