Persisting SQL Agent job history in Managed Instance

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’)

GO 

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

ADD EndTime DATETIME2 NOT NULL DEFAULT (‘99991231 23:59:59.9999999’)

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

ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)

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

ADD CONSTRAINT PK_sysjobhistory PRIMARY KEY (instance_id, job_id, step_id)

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

SET(SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[sysjobhistoryall], DATA_CONSISTENCY_CHECK = ON, HISTORY_RETENTION_PERIOD = 1 MONTH))

GO 

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