Blog Post

Azure SQL Blog
2 MIN READ

Persisting SQL Agent job history in Managed Instance

JovanPop's avatar
JovanPop
Icon for Microsoft rankMicrosoft
Mar 24, 2019

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 
Updated Nov 09, 2020
Version 8.0
  • kevine323's avatar
    kevine323
    Copper Contributor

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

  • alzdba's avatar
    alzdba
    Copper Contributor

    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.

  • prateekagra's avatar
    prateekagra
    Copper Contributor

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