question

mdhar avatar image
0 Votes"
mdhar asked ErlandSommarskog commented

Create an alert when jobs are disabled

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.

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered AmeliaGu-msft edited

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

mdhar avatar image
0 Votes"
mdhar answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ArunKumarGunda-3851 avatar image
0 Votes"
ArunKumarGunda-3851 answered ErlandSommarskog commented

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,
@Server_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 @Server_Name = @@SERVERNAME;
select @Job_Modify_date = getdate();
Select @Job_Modify_by=SUSER_NAME();

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

You didn't read the original post very closely, did you?

mdhar cleary said that an UPDATE trigger was not desired. And for good reasons. Adding triggers to msdb is likely to be unsupported.

I don't really have a good suggestion. Well, you could schedule a job that checks the jobs. But, wait, what if that job is disabled?

Maybe schedule it from Windows Task Manager?

0 Votes 0 ·