Create an alert when jobs are disabled

Question

Hi All,
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. All of our jobs should be enabled to run and are disabled during maintenance periods but may be 'forgot' and left disabled.

Thanks.

Answer 1

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

Answer 2

Hi @AmeliaGu-MSFT I didn't mean to not use msdb.sysjobs but not using a update trigger. Ideally I'm looking for a solution where I will have a config table and it will update every one hour which reads from sysjobs and my alerter reads through that config table to alert if status changed from enabled to disabled.

Answer 3

CREATE trigger [dbo].[Audit_Agent_Job_update] on [msdb].[dbo].[sysjobs]
for INSERT, UPDATE,delete
AS
declare @Job _Name varchar(50), @Job _Created_Date datetime, @Job _Current_Status int,
@Testta _Name varchar(50), @Job _Modify_date datetime,@Job _Modify_by VARCHAR(50);
select @Job _Name=i.name from inserted i;
select @Job _Created_Date=i.date_created from inserted i;
select @Job _Current_Status =i.enabled from inserted i;
select @Testta _Name = @@SERVERNAME;
select @Job _Modify_date = getdate();
Select @Job _Modify_by=SUSER_NAME();