SQL Server Agent Job History Retention

By: Joe Gavin | Updated: 2024-10-03 | Comments (1) | Related: > SQL Server Agent

Problem

The SQL Server Agent is a very powerful job scheduling and alerting tool that's tightly integrated 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 it for much more, i.e., executing multiple step jobs that execute multiple SSIS packages, and you need to retain a longer than the default job history.

Solution

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

All the following examples were created on a newly installed SQL Server 2022 Developer Edition with SQL Server Management Studio (SSMS) 20.2.30.0.

Getting Started

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

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

Next, click History.

SQL Server Agent ->Properties -> 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

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

Make sure that the Limit size of job history log is checked.
Set the Maximum job history log size to 6000.
Keep Maximum job history rows per job at 100.
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

Looking at 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.

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

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

This is the SQL executed that purged the records older than 30 days and made sure the Limit size of job history log stayed 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.

Run this SQL code to create a job that will delete the job history older than 30 days every day at 6:00 AM (can be edited 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

So far, we've seen how to:

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 on the SQL Agent:

About the author

Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2024-10-03

SQL Server Agent Job History Retention Settings