Why would I want to implement this in my environment?
Have you ever wanted to have a job history for SQL Agent jobs? Have you ever wanted to track when a SQL job was added, altered, or deleted? With the changes outlined below you now can have a table where all changes are kept with a history or any changes made to any SQL Agent jobs. Why this solution helps:
- Most SQL jobs are not deployed via a controlled deployment process
- There is no native job audit system within SQL Server
- DevOps may make changes and not track the changes formally
- Job changes can be tracked and kept within a database backup and job history can be retained
Creating database objects to capture SQL Agent job changes
There are four steps required to setup this process:
- Create a SEQUENCE database object for the primary key for the table
- Create the table to store the SQL Agent job changes that uses the SEQUENCE object as a primary key
- Create the stored procedure to capture the changes
- Create a SQL Agent job that will run periodically throughout the day to capture any SQL jobs changes
Step 1: Create a SEQUENCE database object that will be used primary key in the table
Create a Sequence database object in the MSDB database where the table is to be created that will be used as a primary key in the table that will be created in step 2. Below is the code to create a SEQUENCE object within the MSDB database.
USE MSDB GO CREATE SEQUENCE [dbo].[SQL_Agent_Backups_SQN] AS [bigint] START WITH 0 INCREMENT BY 1 MINVALUE -9223372036854775808 MAXVALUE 9223372036854775807 CACHE 100000 GO
Step 2: Create the table to store the SQL Agent job changes that uses the SEQUENCE object as a primary key
The script below creates the table that will capture any job change that are recorded in the SQL Agent ecosystem when any job is added, altered, or deleted. This table uses the SEQUENCE object created in step 1 above as its primary key. This table is created as a temporal table to keep track of the SQL Agent current job settings and in the event the SQL Agent job setting has changed, the history of that job can be tracked in the temporal table to find the job history. There is an example of how to query this temporal table with a job name at the end of this article. Note: The "JobHashId" is the column that stores the hash of the column data values that is used to compare the hash of the columns that are being tracked to the current value that exist in the dbo.sysjobs and dbo.sysjobsteps in the MSDB database. This usage will be more clearly illustrated in step 3.
USE MSDB
GO
CREATE TABLE [dbo].[SQL_Agent_Backups]
(
SQL_Agent_BackupsId INT NOT NULL
CONSTRAINT [df_dbo_SQL_Agent_BackupsId]
DEFAULT (NEXT VALUE FOR [dbo].[SQL_Agent_Backups_SQN]),
JobHashId BINARY(20) NULL,
JobId UNIQUEIDENTIFIER NULL,
JobName SYSNAME NULL,
JobDescription NVARCHAR(512) NULL,
JobDateCreated DATETIME NULL,
JobDateModified DATETIME NULL,
JobStepName SYSNAME NULL,
JobStepSubsystem NVARCHAR(40) NULL,
JobStepCommand NVARCHAR(4000) NULL,
JobVersionNumber INT NULL,
[row_start_date] DATETIME2(2) GENERATED ALWAYS AS ROW START NOT NULL,
[row_end_date] DATETIME2(2) GENERATED ALWAYS AS ROW END NOT NULL,
CONSTRAINT [idx_OBJECT_BACKUP_SQL_Agent_Backups_pk]
PRIMARY KEY ([SQL_Agent_BackupsId] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF
) ON [PRIMARY],
PERIOD FOR SYSTEM_TIME([row_start_date], [row_end_date])
) ON [PRIMARY]
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[SQL_Agent_Backups_HISTORY]))
GO
-- Create Nonclustered index on JobHashId for faster joins within the stored procedure
CREATE NONCLUSTERED INDEX [ndx_OBJECT_BACKUP_SQL_Agent_Backups_JobHashId]
ON [dbo].[SQL_Agent_Backups] ([JobHashId] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF
)
ON [PRIMARY]
GO
Step 3: Create the stored procedure to capture the changes in the SQL Agent jobs environment
The stored procedure, [dbo].[usp_SQL_Agent_Backups], is the mechanism responsible for performing the comparison between the existing SQL Agent jobs and the contents of the table created in step 2 above. The comparison is done by doing a HASHBYTES on the columns that are to be tracked for any change and compare to the JobHashId column of the table created in step 2 above.
USE MSDB
GO
CREATE OR ALTER PROCEDURE [dbo].[usp_SQL_Agent_Backups]
AS
/* This stored procedure perform as check to see if a job has changed from the base table capture in the [dbo].[SQL_Agent_Backups] table.
If a job has changed, the original job will be put in the [dbo].[usp_SQL_Agent_Backups_HISTORY] table
and the new job will be put into the [dbo].[usp_SQL_Agent_Backups] table. */SET NOCOUNT, XACT_ABORT ON;
BEGIN
DROP TABLE IF EXISTS #RawJobData
SELECT JobHashId = TRY_CONVERT(
BINARY(20),
HASHBYTES(
N'SHA1',
(LTRIM(RTRIM(ISNULL(TRY_CONVERT(NVARCHAR(32), sj.job_id), N''))))
+ NCHAR(31)
+ LTRIM(RTRIM(ISNULL(TRY_CONVERT(NVARCHAR(255), sj.[name]), N'')))
+ NCHAR(31)
+ LTRIM(RTRIM(ISNULL(
TRY_CONVERT(NVARCHAR(512), sj.[description]),
N''
)
)
) + NCHAR(31)
+ LTRIM(RTRIM(ISNULL(TRY_CONVERT(NVARCHAR(40), sj.date_created), N'')))
+ NCHAR(31)
+ LTRIM(RTRIM(ISNULL(
TRY_CONVERT(NVARCHAR(255), sj.date_modified),
N''
)
)
) + NCHAR(31)
+ LTRIM(RTRIM(ISNULL(TRY_CONVERT(NVARCHAR(255), SJS.step_name), N'')))
+ NCHAR(31)
+ LTRIM(RTRIM(ISNULL(TRY_CONVERT(NVARCHAR(40), SJS.subsystem), N'')))
+ NCHAR(31)
+ LTRIM(RTRIM(ISNULL(TRY_CONVERT(NVARCHAR(4000), SJS.[command]), N'')))
+ NCHAR(31)
+ LTRIM(RTRIM(ISNULL(
TRY_CONVERT(NVARCHAR(255), sj.version_number),
N''
)
)
)
)
-- END OF HASHBYTES
, 2
),
-- END OF TRY_CONVERT
JobId = sj.job_id,
JobName = sj.[name],
JobDescription = sj.[description],
JobDateCreated = sj.date_created,
JobDateModified = sj.date_modified,
JobStepName = SJS.step_name,
JobStepSubsystem = SJS.subsystem,
JobStepCommand = SJS.[command],
JobVersionNumber = sj.version_number
INTO #RawJobData
FROM msdb.dbo.sysjobs AS sj
JOIN msdb.dbo.sysjobsteps AS SJS
ON sj.job_id = SJS.job_id
-- WHERE sj.[name] IN ('<jobs names for specific jobs>')
/************* Review temp table if necessary *******************************
SELECT JobHashId
, JobId
, JobName
, JobDescription
, JobDateCreated
, JobDateModified
, JobStepName , JobStepSubsystem , JobStepCommand
, JobVersionNumber
FROM #RawJobData
*******************************************************************************/
/******************** DELETE old job data from [dbo].[SQL_Agent_Backups] **************/ DELETE SAB WITH (TABLOCK)
-- SELECT SAB.*
FROM [dbo].[SQL_Agent_Backups] AS SAB
LEFT JOIN #RawJobData AS RJD
ON SAB.JobHashId = RJD.JobHashId
WHERE RJD.JobHashId IS NULL
/******************* INSERT any changed jobs or new jobs ***************************************/ INSERT [dbo].[SQL_Agent_Backups] WITH (TABLOCK)
(
[JobHashId],
[JobId],
[JobName],
[JobDescription],
[JobDateCreated],
[JobDateModified],
[JobStepName],
[JobStepSubsystem],
[JobStepCommand],
[JobVersionNumber]
)
SELECT RJD.[JobHashId],
RJD.[JobId],
RJD.[JobName],
RJD.[JobDescription],
RJD.[JobDateCreated],
RJD.[JobDateModified],
RJD.[JobStepName],
RJD.[JobStepSubsystem],
RJD.[JobStepCommand],
RJD.[JobVersionNumber]
FROM #RawJobData AS RJD
LEFT JOIN [dbo].[SQL_Agent_Backups] AS SAB
ON SAB.JobHashId = RJD.JobHashId
WHERE SAB.JobHashId IS NULL
ENDStep 4: Create a SQL Agent job that will run periodically throughout the day to capture any SQL jobs changes
The final step to implement this process is to create a SQL Agent job to run the "[dbo].[usp_SQL_Agent_Backups]" stored procedure periodically to check for any changes to the SQL Agent jobs system. In our current environment, this stored procedure is set to run every ten minutes to capture any changes in that window of time. With the use of hashing to identify any changes within the SQL Agent system to the storage table, this process only takes milliseconds. If your system does not have many jobs or does not change very often, perhaps you can run this once a day or even once a week.
Query examples for table data in [dbo].[SQL_Agent_Backups] table
Below are some query examples that show the utilization of the temporal tables to identify job changes with an environment.
Find job changes
This query below will show the most changed jobs in the SQL Agent ecosystem
SELECT JobId,
JobName,
JobStepName,
RCOUNT = COUNT(*)
FROM [msdb].[dbo].[SQL_Agent_Backups] FOR SYSTEM_TIME ALL
GROUP BY JobId,
JobName,
JobStepName
HAVING COUNT(*) > 1
ORDER BY 4 DESCOutput of the query above using anonymized data
JobId JobName JobStepName RCOUNT ------------------------------------ --------- ---------------- ------ EA603FDF-FDFD-478E-807A-F6F4D98D3ACB JobName 1 Job Step Name 1 20 33644DAD-6D32-47AD-BDEA-58CEC46072F8 JobName 2 Job Step Name 2 19
Find job changes for a specific job and step name
The query below will show all the job changes for a given SQL Agent job name.
SELECT * FROM [msdb].[dbo].[SQL_Agent_Backups] FOR SYSTEM_TIME ALL WHERE JobName = '<Job Name>' ORDER BY JobVersionNumber DESC
Output of the query above using anonymized data showing the job history for a current job.
SQL_Agent_BackupsId JobHashId JobId JobName JobDescription JobDateCreated JobDateModified JobStepName JobStepSubsystem JobStepCommand JobVersionNumber row_start_date row_end_date ------------------- --------------------------------------- ------------------------------------ -------- ----------------------- ----------------------- ---------------------- -------------------- ---------------- -------------------------------------------------- ---------------- ----------------------- ---------------------- 19168 0x513655DDB33730103A8D9A5412A6B3767CF4AC1 EA603FDF-FDFD-478E-807A-F6F4D98D3ACB Job Name Description of job 2026-02-04 13:48:23.317 2026-04-01 09:41:55.717 Job step information PowerShell Command contained with the SQL Agent job 366 366 2026-04-01 14:50:00.36 9999-12-31 23:59:59.99 19169 0x93F96854C373E39133611FD687EC391728A1177 EA603FDF-FDFD-478E-807A-F6F4D98D3ACB Job Name Description of job 2026-02-04 13:48:23.317 2026-04-01 09:41:55.717 Job step information PowerShell Command contained with the SQL Agent job 366 366 2026-04-01 14:50:00.36 9999-12-31 23:59:59.99 19170 0xE424BB2A4ABDD5E064E333F707D32BBA64747F EA603FDF-FDFD-478E-807A-F6F4D98D3ACB Job Name Description of job 2026-02-04 13:48:23.317 2026-04-01 09:41:55.717 Job step information PowerShell Command contained with the SQL Agent job 366 366 2026-04-01 14:50:00.36 9999-12-31 23:59:59.99 19171 0x51E209CD7E6C04CA66BC3F14BBE0F05B8E44969 EA603FDF-FDFD-478E-807A-F6F4D98D3ACB Job Name Description of job 2026-02-04 13:48:23.317 2026-04-01 09:41:55.717 Job step information PowerShell Command contained with the SQL Agent job 366 366 2026-04-01 14:50:00.36 9999-12-31 23:59:59.99 19172 0xAE14BAD945CCEFEB150027211CB15E46E207ECFD EA603FDF-FDFD-478E-807A-F6F4D98D3ACB Job Name Description of job 2026-02-04 13:48:23.317 2026-04-01 09:41:55.717 Job step information PowerShell Command contained with the SQL Agent job 366 366 2026-04-01 14:50:00.36 9999-12-31 23:59:59.99 19173 0x7BFC15D01AF5F99FA0B8FD0AB4DBC9B2FAA2EC31 EA603FDF-FDFD-478E-807A-F6F4D98D3ACB Job Name Description of job 2026-02-04 13:48:23.317 2026-04-01 09:41:55.717 Job step information PowerShell Command contained with the SQL Agent job 366 366 2026-04-01 14:50:00.36 9999-12-31 23:59:59.99 19174 0xC47E96BCB50A53B31FC43B36B2427A565E440F73 EA603FDF-FDFD-478E-807A-F6F4D98D3ACB Job Name Description of job 2026-02-04 13:48:23.317 2026-04-01 09:41:55.717 Job step information PowerShell Command contained with the SQL Agent job 366 366 2026-04-01 14:50:00.36 9999-12-31 23:59:59.99 19175 0xD93EA7B5E22B69C321E3485E3A22442B512E2E63 EA603FDF-FDFD-478E-807A-F6F4D98D3ACB Job Name Description of job 2026-02-04 13:48:23.317 2026-04-01 09:41:55.717 Job step information PowerShell Command contained with the SQL Agent job 366 366 2026-04-01 14:50:00.36 9999-12-31 23:59:59.99 19176 0x94FF00D0B1F34DB600D9E91B75364335B1D4B459 EA603FDF-FDFD-478E-807A-F6F4D98D3ACB Job Name Description of job 2026-02-04 13:48:23.317 2026-04-01 09:41:55.717 Job step information PowerShell Command contained with the SQL Agent job 366 366 2026-04-01 14:50:00.36 9999-12-31 23:59:59.99 19177 0x7A2439B5E3DCAAE269CE395FC69A0C531DB3FBF7 EA603FDF-FDFD-478E-807A-F6F4D98D3ACB Job Name Description of job 2026-02-04 13:48:23.317 2026-04-01 09:41:55.717 Job step information PowerShell Command contained with the SQL Agent job 366 366 2026-04-01 14:50:00.36 9999-12-31 23:59:59.99 19178 0x110FFB0543374D66054CD3E4FC2779CD11C68E77 EA603FDF-FDFD-478E-807A-F6F4D98D3ACB Job Name Description of job 2026-02-04 13:48:23.317 2026-04-01 09:41:55.717 Job step information PowerShell Command contained with the SQL Agent job 366 366 2026-04-01 14:50:00.36 9999-12-31 23:59:59.99 19179 0x743EBBE4DD90E17795A4BBE6AD711CB5EAFA5DED EA603FDF-FDFD-478E-807A-F6F4D98D3ACB Job Name Description of job 2026-02-04 13:48:23.317 2026-04-01 09:41:55.717 Job step information PowerShell Command contained with the SQL Agent job 366 366 2026-04-01 14:50:00.36 9999-12-31 23:59:59.99 19180 0x5CB6729A1C03TBEF7DAB569426130034C580FDEC EA603FDF-FDFD-478E-807A-F6F4D98D3ACB Job Name Description of job 2026-02-04 13:48:23.317 2026-04-01 09:41:55.717 Job step information PowerShell Command contained with the SQL Agent job 366 366 2026-04-01 14:50:00.36 9999-12-31 23:59:59.99 19181 0x957913DBE60F26A91C8F5CD72EF25DBD15A85B7D EA603FDF-FDFD-478E-807A-F6F4D98D3ACB Job Name Description of job 2026-02-04 13:48:23.317 2026-04-01 09:41:55.717 Job step information PowerShell Command contained with the SQL Agent job 366 366 2026-04-01 14:50:00.36 9999-12-31 23:59:59.99 19182 0x1D3BB60B4CC200CFAD35A807B3B147CEC629A10 EA603FDF-FDFD-478E-807A-F6F4D98D3ACB Job Name Description of job 2026-02-04 13:48:23.317 2026-04-01 09:41:55.717 Job step information TSQL Command contained with the SQL Agent job 366 366 2026-04-01 14:50:00.36 9999-12-31 23:59:59.99 19183 0x6DE0C1E7D19BA2E33ACA68719B3E6F0AF99F0980 EA603FDF-FDFD-478E-807A-F6F4D98D3ACB Job Name Description of job 2026-02-04 13:48:23.317 2026-04-01 09:41:55.717 Job step information TSQL Command contained with the SQL Agent job 366 366 2026-04-01 14:50:00.36 9999-12-31 23:59:59.99 18730 0x840C8D1B1FDD9339A770C405143DBD37423BA4 EA603FDF-FDFD-478E-807A-F6F4D98D3ACB Job Name Description of job 2026-02-04 13:48:23.317 2026-04-01 08:41:59.710 Job step information PowerShell Command contained with the SQL Agent job 355 355 2026-04-01 13:50:00.73 2026-04-01 14:50:00.34 18731 0x68BB9A9F387FA1947E7AE24CFB5957565E872DE EA603FDF-FDFD-478E-807A-F6F4D98D3ACB Job Name Description of job 2026-02-04 13:48:23.317 2026-04-01 08:41:59.710 Job step information PowerShell Command contained with the SQL Agent job 355 355 2026-04-01 13:50:00.73 2026-04-01 14:50:00.34 18732 0xE2049F6E1270F9E6B1F3332BC2DB4349B14603E8 EA603FDF-FDFD-478E-807A-F6F4D98D3ACB Job Name Description of job 2026-02-04 13:48:23.317 2026-04-01 08:41:59.710 Job step information PowerShell Command contained with the SQL Agent job 355 355 2026-04-01 13:50:00.73 2026-04-01 14:50:00.34 18733 0xECAD0B7A7F76048CA471BDA3B9F2454012B9C919 EA603FDF-FDFD-478E-807A-F6F4D98D3ACB Job Name Description of job 2026-02-04 13:48:23.317 2026-04-01 08:41:59.710 Job step information PowerShell Command contained with the SQL Agent job 355 355 2026-04-01 13:50:00.73 2026-04-01 14:50:00.34
Other coding exercises for this article
For this article all SQL Agent job information is captured into a table in the MSDB database and this may not be best for your environment. This code pattern could be customized to stored in a given user database to only track jobs that are relevant to that user database. Within the stored procedure code base, [dbo].[usp_SQL_Agent_Backups], I left in commented lines to filter only specific jobs if this is desired in your environment. Below are other options that could also be replaced if an older version of SQL Server is being used in the environment.
- Sequence object --> Identity value primary key
- Temporal table --> Separate table for main jobs and job history
