Hi @mdhar ,
I'm looking to create an alert with an alternate way but not an update trigger on msdb.sysjobs when sql agent jobs are disabled.
I’m afraid we cannot create an alert by not using msdb.sysjobs, because job information is held in the dbo.sysjobs table in the msdb database.
You can try to create an audit to track disabled job, but the database audit specification still need to add update action on the dbo.sysjobs table.
For example:
-- Audit
CREATE SERVER AUDIT [jobs]
TO FILE
( FILEPATH = N'PathToSomeFolder'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
,AUDIT_GUID = 'e807469a-6c9d-43f1-af46-cf7b89ba898d'
)
ALTER SERVER AUDIT [jobs] WITH (STATE = ON)
GO
USE [msdb]
GO
CREATE DATABASE AUDIT SPECIFICATION [job_changes]
FOR SERVER AUDIT [jobs]
ADD (UPDATE ON OBJECT::[dbo].[sysjobs] BY [public]),
ADD (UPDATE ON OBJECT::[dbo].[sysjobsteps] BY [public]),
ADD (UPDATE ON OBJECT::[dbo].[sysjobschedules] BY [public])
WITH (STATE = ON)
GO
Please refer to this thread which might help.
Best Regards,
Amelia
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread