How to persist SQL Agent job history in Azure SQL Managed Instance


How to persist SQL Agent job history in Azure SQL Managed Instance

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

The Challenge
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

Step 1:- Adding Time Columns
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] 

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

Step 4:- Enabling History Saving
This step instructs the table to save changes in a dedicated history table, ensuring data integrity.
ALTER TABLE [msdb].[dbo].[sysjobhistory] 
    HISTORY_TABLE = [dbo].[sysjobhistoryall], 

Verifying Our Solution
To test our setup, let's pretend we're cleaning up some old job details:
EXEC msdb.dbo.sp_purge_jobhistory;

Even after this cleanup, we'll still be able to access the deleted job history records in the 'sysjobhistoryall' table.
select * from msdb.dbo.sysjobhistoryall 

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.
Lince Sebastian

Indian MSSQL DBA thriving in database management - ensuring efficiency and smooth operations. Devoted father of two and avid Clash of Clans player, driven by strategic thinking. Football fuels my passion - cheering Kerala Blasters and Arsenal. I share my professional and life insights through my blog.


Post a Comment (0)
Previous Post Next Post