SQL Server Agent Job History Retention

Problem

The SQL Server Agent is a very powerful job scheduling and alerting tool that tightly integrates with SQL Server. It’s quite possible you’re only using it for basic maintenance tasks like database backups, index maintenance, DBCC checks, etc., and the default retention is fine. However, you may also be using SQL Server Agent for much more, e.g., executing multiple step jobs that execute multiple SSIS packages and retaining a job history that’s longer than the default.

Solution

We’ll look at how to change the retention through SQL Server Management Studio and T-SQL.

To clarify, I will use a newly installed SQL Server 2022 Developer Edition with SQL Server Management Studio (SSMS) 20.2.30.0 to create the following examples.

Getting Started

The job history retention settings are located under the SQL Agent Properties:

1. Expand the SQL Server dropdown menu in SSMS Object Explorer.
2. Right-click SQL Server Agent.
3. Choose Properties.

1. Next, click History.

The image below is the History screen.

Current Job Log Size History (in Rows)

Looking at the first section, Current job log size history (in rows), we see it’s enabled by default. The default value for the Maximum job history log size (in rows) is 1000, and the Maximum job history rows per job is 100.

Run this query to see the highest number of rows stored by a job per day:

SELECT TOP 1 COUNT(*)  AS [MaxJobsRowPerDay]
FROM  [msdb].[dbo].[sysjobhistory] 
GROUP BY [run_date]
ORDER BY COUNT(*) DESC;
GO

Let’s say we want to retain 30 days of history. We multiply the expected number of rows for 1 day by 30 days. 198 * 30 = 5,940.

This query will tell us the highest number of rows per job:

SELECT TOP 1 MAX([step_id]) AS [MaxRowsPerJob]
FROM [msdb].[dbo].[sysjobhistory];
GO

For this case, the value set for the Maximum job history rows per job(100) is fine.

1. Check the Limit size of job history log.
2. Set the Maximum job history log size to 6000.
3. Keep Maximum job history rows per job at 100.
4. Click OK.

Or execute the following T-SQL:

USE [msdb]
GO
 
EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows = 6000,
                                         @jobhistory_max_rows_per_job = 100;
GO

We’ll retain approximately 30 days of history based on current usage.

Remove Agent History

Reviewing the bottom part of the screen, a logical person may look at the Remove agent history checkbox and think they can just check it instead of using the Limit size of job history log and set the retention to what you want, automatically keeping the log pruned.

Let’s try it and see what happens.

1. Uncheck Current size of job history log.
2. Check Remove agent job history.
3. Choose Day(s), Weeks(s), or Months(s).
4. Click OK.

Go back to the History screen. The first thing we see is that the Limit size of job history log remains unchecked, but everything else is back to its default values. So, all that happened was that we manually deleted history that was older than 30 days once. The process will not repeat itself.

Below is the SQL that executed that purged the records older than 30 days, and kept the Limit size of job history log unchecked:

-- use msdb
USE [msdb];
GO
 
-- set retention in number of days
DECLARE @PurgeDate DATETIME = DATEADD(D, -30, GETDATE())
 
-- purge old records
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = @PurgeDate
GO
 
-- uncheck ‘Limit size of job history log'
EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows = -1,
                                         @jobhistory_max_rows_per_job = -1
GO

This is likely something you would want to automate.

Thus, run this SQL code to create a job to delete the job history older than 30 days every day at 6:00 AM (edit for your needs).

USE [msdb]
GO
 
/****** Object:  Job [PurgeOldSqlagentJobHistory]    Script Date: 7/31/2024 6:05:26 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 7/31/2024 6:05:26 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 
END
 
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'PurgeOldSqlagentJobHistory', 
      @enabled=1, 
      @notify_level_eventlog=0, 
      @notify_level_email=0, 
      @notify_level_netsend=0, 
      @notify_level_page=0, 
      @delete_level=0, 
      @description=N'Purges SQL Agent Job history over a specified number of days.', 
      @category_name=N'[Uncategorized (Local)]', 
      @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [DeleteHistory]    Script Date: 7/31/2024 6:05:26 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DeleteHistory', 
      @step_id=1, 
      @cmdexec_success_code=0, 
      @on_success_action=1, 
      @on_success_step_id=0, 
      @on_fail_action=2, 
      @on_fail_step_id=0, 
      @retry_attempts=0, 
      @retry_interval=0, 
      @os_run_priority=0, @subsystem=N'TSQL', 
      @command=N'-- use msdb
USE [msdb];
GO
 
-- set retention in number of days
DECLARE @PurgeDate DATETIME = DATEADD(D, -30, GETDATE())
 
-- purge old records
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = @PurgeDate
GO
 
-- uncheck ‘Limit size of job history log''
EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows = -1,
                                         @jobhistory_max_rows_per_job = -1
GO
', 
      @database_name=N'master', 
      @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'PurgeOldSqlagentJobHistory_sched', 
      @enabled=1, 
      @freq_type=4, 
      @freq_interval=1, 
      @freq_subday_type=1, 
      @freq_subday_interval=0, 
      @freq_relative_interval=0, 
      @freq_recurrence_factor=0, 
      @active_start_date=20240724, 
      @active_end_date=99991231, 
      @active_start_time=60000, 
      @active_end_time=235959, 
      @schedule_uid=N'6fb90b53-9a6a-4aed-bac9-d6eaf3c2587c'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Next Steps

• Limit the number of rows of job history for SQL Agent to keep.
• Limit the number of rows of job history for SQL Agent to keep per Job.
• Manually delete SQL Agent history older than a certain period via SQL Agent and T-SQL.
• Automate deleting SQL Agent history older than a certain period via a SQL Agent Job.

Here are links to several more MSSQLTips articles on the SQL Agent:

• Verbose SQL Server Agent Logging
• Auditing for New SQL Server Agent Jobs
• SQL Server Agent Job Ownership
• SQL Server Agent Jobs Without an Operator
• SQL Server Agent Job Management
• Querying SQL Server Agent Job Information
• How to Change the SQL Server Agent Log File Path
• Enable SQL Server Agent Mail Profile
• Custom Job Categories to Organize your SQL Agent Jobs
• Understanding How SQL Server Agent Jobs Can Share Schedules