Persisting SQL Agent job history in Managed Instance

Published Mar 23 2019 06:46 PM 2,313 Views

First published on MSDN on Sep 20, 2018
Azure SQL Managed Instance is a fully managed SQL Server hosted in Azure cloud. Although it supports many SQL Server features, there are some constraints compared to SQL Server that you manage. One of the constraint that might be an issue is the fact that SQL Agent keeps a limited history of job executions that cannot be changed. In this post you will see one way to workaround this.

SQL Agent in Managed Instance can keep limited job history 10 job history records per each job step and total of 1000 history record. You can see this info in the columns jobhistory_max_rows_per_job and jobhistory_max_rows_per_job if you execute the following procedure:
exec msdb.dbo.sp_get_sqlagent_properties
Azure SQL Managed Instance don't allows you to change SQL Agent properties because they are stored in the underlying  registry values. On side-effect of this is that you have fixed retention policy for job history records - 1000 total records and max 100 history records per job. This means that you might loose older job history for some jobs.

If you want to preserve these information you would need to save information from sysjobhistory table. In this post I will show you how to do it with temporal tables.

Temporal tables

Temporal tables are special type of the tables in Azure SQL Database Managed Instance that preserve all changes made in the table. There is a shadow history table where will be placed copies of deleted and updated rows from the main table once they are changed. This might be good solution for persisting job history, because if you convert sysjobhistory table to temporal table, all purged job history rows will be preserved.

Adding temporal feature to sysjobhistory

In order to convert standard sysjobhistory table to temporal table you can use the following script:

ALTER TABLE [msdb].[dbo].[sysjobhistory] 
ADD StartTime DATETIME2 NOT NULL DEFAULT ('19000101 00:00:00.0000000') 

ALTER TABLE [msdb].[dbo].[sysjobhistory] 
ADD EndTime DATETIME2 NOT NULL DEFAULT ('99991231 23:59:59.9999999') 

ALTER TABLE [msdb].[dbo].[sysjobhistory] 

ALTER TABLE [msdb].[dbo].[sysjobhistory] 
ADD CONSTRAINT PK_sysjobhistory PRIMARY KEY (instance_id, job_id, step_id) 

ALTER TABLE [msdb].[dbo].[sysjobhistory] 

The only bigger change in this script is the fact that primary key is added to jobhistory table and there are two additional columns required to track when the changes are made.

Reading full history of job executions

With the temporal table, you can test whether the changes are preserved once Managed Instance clean-up job history. You can use the following procedure to simulate this:

EXEC msdb.dbo.sp_purge_jobhistory 

Once you purge your job history, you can read job history records that are deleted in the sysjobhistoryall table:

select * from msdb.dbo.sysjobhistoryall 
Established Member

Is this still the recommended way to persist Managed Instance job history?

Frequent Visitor

The workaround works, but is useless because you'll have to code everything to visualize it all ( e.g. SSMS alike )


The needed modification should not be an issue at all for the SQLServer DEV team! Even it it were to just extend to total number of rows to 1 million on all Azure Managed Instances. Hence, giving some use for the history for consumers.

Occasional Visitor

Workaround is perfect ,We are Hoping SQLDev Team Will remove this constraint in future.

Frequent Visitor

For what it's worth, I've created a very simple SSMS report to query a given jobs jobhistory in the persisted table:

You can find it ( with my other SSMS custom reports ) in my github :

Version history
Last update:
‎Nov 09 2020 09:43 AM
Updated by: