Retaining Job History in Managed Instances

In a Managed Instance, the SQL Agent job history is fixed at 1000 records or 100 records per job. This isn’t configurable like a regular SQL Server install. So how can we maintain a history of these if we want to retain those records?

There are 3 approaches which could be worth considering. Two of these have been well covered by others and the final one I’ll demonstrate here:

• Making the sysjobhistory table Temporal to automatically keep the history. This is a very concise and effective change, as detailed by Jovan Popovic
• Creating a job to copy the data into a new table which will persist the details. This solution from Aaron Bertrand also includes robust retention rules to to customise the process
• Create a trigger on the table and take a copy of the records as they’re added

I really like both of the solutions from Jovan and Aaron but if we left it at that it would be a short post eh. Let’s take a look at the trigger option for comparison.

The rationale for looking at triggers was to be able to handle the data little and often rather than a batch process which could miss some records if it wasn’t ran frequently enough. This would have a similar result to the temporal table solution but a custom trigger allows us to define which fields to retain and have the option to place that table in a different database if needed.

There are only 3 components we’ll need for this solution:

1. Table to retain the history
2. Trigger to intercept INSERT commands
3. Job to maintain a reasonable data volume

Let’s get to it.

Firstly we’ll create a table to retain the details we need. This will only be a subset of the available fields, and we’ll handle the run date, time and duration in here a little differently:

CREATE TABLE [dbo].[sysjobarchive] (
	[instance_id] INT NOT NULL,
	[job_id] UNIQUEIDENTIFIER NOT NULL,
	[step_id] INT NOT NULL,
	[run_status] INT NOT NULL,
	[run_datetime] DATETIME NOT NULL,
	[run_durationseconds] INT NOT NULL,
	CONSTRAINT PK_sysjobarchive PRIMARY KEY ([instance_id])
);

Next up we’ll create a simple trigger to grab any INSERT commands to the table so that we can replicate in our archive.

The online documentation indicates that updates may take place but without any details. From some testing I haven’t seen any of these so we’re only triggering on insert.

The run date, time and duration aren’t well formed in the regular table so we’ll be borrowing (stealing) from Aaron’s solution to format them more effectively:

CREATE OR ALTER TRIGGER [TG_SysJobHistory_Insert]
ON dbo.sysjobhistory
AFTER INSERT
AS
BEGIN
 
 INSERT INTO [dbo].[sysjobarchive] (
	[instance_id],
	[job_id],
	[step_id],
	[run_status],
	[run_datetime],
	[run_durationseconds]
	)
	SELECT
	[instance_id],
	[job_id],
	[step_id],
	[run_status],
	CONVERT(DATETIME,
		CONVERT(varchar(4), [run_date] / 10000)
		+ CONVERT(varchar(2), RIGHT('0' + RTRIM([run_date] % 10000 / 100),2))
		+ CONVERT(varchar(2), RIGHT('0' + RTRIM([run_date] % 100),2))
		+ ' ' + CONVERT(varchar(2), RIGHT('0' + RTRIM([run_time] / 10000),2))
		+ ':' + CONVERT(varchar(2), RIGHT('0' + RTRIM([run_time] % 10000 / 100),2))
		+ ':' + CONVERT(varchar(2), RIGHT('0' + RTRIM([run_time] % 100),2))),
	[run_duration] / 1000000 * 24 * 60 * 60
		+ [run_duration] / 10000  % 100 * 60 * 60
		+ [run_duration] / 100 % 100 * 60
		+ [run_duration] % 100
	FROM inserted;
 
END

With that in place we’ll be capturing all the history but we might not want to keep it forever. We can slide a simple deletion into a SQL Agent job or maintenance task to keep it tidy:

/* Change as needed */
DECLARE @RetentionDays INT = 7;

DELETE
FROM [msdb].[dbo].[sysjobarchive]
WHERE [run_datetime] < DATEADD(DAY, -(@RetentionDays), GETDATE());

Depending on the fields you’re retaining and volume of records you may want to batch those deletions or add an index to the run_datetime field.

For bonus points you could borrow the customisation from Aaron’s post and integrate that into the retention to make it more flexible.

Wrap up

In this post we’ve looked at how to overcome the limitation on SQL Agent Job history in a Managed Instance. We’ve achieved that through a trigger to intercept new records and maintain a new table with those details.

I recently had this challenge when running jobs excessively as part of some performance testing on a Managed Instance. I wondered where my history had gone so after finding the retention wasn’t configurable I took this approach with a trigger which worked well for my needs.

This is simply one way to approach the problem. Jovan’s solution is much more concise, and Aaron’s solution is very customisable. Whichever you choose its all on a plate for you, enjoy!