Azure SQL Managed Instance provides a robust environment for running SQL Server workloads in the cloud. However, it does come with certain limitations, one of which revolves around the retention of SQL Agent job history. In this blog post, we'll explore a clever workaround to ensure that you don't lose critical job execution details.
Understanding Limited Job History
In Azure SQL Managed Instance, SQL Agent keeps track of job execution history, but there's a catch. It maintains a limited number of records - specifically, only the last 10 executions for each job step, with a total cap of 1000 records. You can verify these limits by using a procedure:
exec msdb.dbo.sp_get_sqlagent_properties
Unlike SQL Server instances you manage yourself, Managed Instance doesn't allow you to tweak SQL Agent properties. This means you're bound by default retention policies - a maximum of 1000 total records and no more than 100 records for each job. This restriction could potentially lead to the loss of older job history.
Leveraging Temporal Tables
To overcome this limitation, we'll utilize a feature called "temporal tables". These tables act as a historical record, capturing all changes made to the main table. It's akin to having a built-in time machine for your data!
Transforming 'sysjobhistory' into a Temporal Table
Let's go through the steps to convert the standard sysjobhistory table into a temporal
These newly added columns will help us keep track of when changes occur.
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');
Step 2:- Setting Up Time Tracking
This command establishes a system for tracking changes over time.
ALTER TABLE [msdb].[dbo].[sysjobhistory] ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime);
Step 3:- Adding a Primary Key
This primary key ensures quick and efficient retrieval of specific job details.
ALTER TABLE [msdb].[dbo].[sysjobhistory] ADD CONSTRAINT PK_sysjobhistory PRIMARY KEY (instance_id, job_id, step_id);
This step instructs the table to save changes in a dedicated history table, ensuring data integrity.
ALTER TABLE [msdb].[dbo].[sysjobhistory]
SET (SYSTEM_VERSIONING = ON (
HISTORY_TABLE = [dbo].[sysjobhistoryall],
DATA_CONSISTENCY_CHECK = ON,
HISTORY_RETENTION_PERIOD = 1 MONTH)
);
To test our setup, let's pretend we're cleaning up some old job details:
Even after this cleanup, we'll still be able to access the deleted job history records in the 'sysjobhistoryall' table.
Managing job history in Azure SQL Managed Instance is crucial for tracking the progress of your tasks. With these steps, you'll have a reliable system in place, ensuring that no job execution detail goes unnoticed.
EXEC msdb.dbo.sp_purge_jobhistory;
select * from msdb.dbo.sysjobhistoryall
Tags:
TSQL