Blog Post

SQL Agent job history on a Managed Instance

,

The defaults for saving SQL Agent Job history are ok (at best), so you should probably check and update them if needed. Sadly, if you are using a Managed Instance this isn’t an option.

SQL Managed Instance currently doesn’t allow you to change any SQL Agent properties because they are stored in the underlying registry values.

Now this might change in the future, but I wouldn’t hold my breath. Fortunately, Jovan Popovic on Microsoft’s Techcommunity site gave what I thought was a really cool solution. He provides the code to convert the table msdb.dbo.sysjobhistory into a temporal table to store all of the history into a separate table called msdb.dbo.sysjobhistoryall. I highly recommend going and reading his post but just in case you don’t want to here is the basic code he recommends using.

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 

To encourage you to go to his post I’m going to mention that in the comments there is a link to a SSMS report someone wrote to more easily read the information out of the table. I had to make a minor change to the history table it pulled from but it works pretty well.

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating