question

afzaalawan avatar image
afzaalawan asked

SQL Job schedule got disabled

Hi,

I am trying to investigate why

all jobs for a particular database were detached from schedule (though jobs were not disabled) but did not trigger having no schedule. (I have attached a screen shot, to identify the Enable check box)

Any thoughts to investigate where shall I look. I could not find any clues from default trace, but have saved default trace into a table

Onwards I am going to setup an Audit. 1665157178483.png


Hint.

any changes in schedule will modify "msdb.dbo.sysschedules" and we can find the last modification but unable to check previous -- we should have some thing in default trace data.

select name, date_created, date_modified from msdb.dbo.sysschedules where CAST(date_modified AS DATE) = '2022-10-06'

sql-server-2012sql-agentschedule
1665157178483.png (31.9 KiB)
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
anthony.green avatar image
anthony.green answered

You would need some form of Audit.
Server Side audit or DDL trigger
Database level audit or DDL trigger

Would need to be on the BATCH_COMPLETED_GROUP action group type as there is no audit category for job / schedule changes.


The default trace doesn't capture any changes to jobs or their schedules so that wouldn't be on to look at.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.