Determine if job executed as part of schedule RRS feed

  • Question

  • Hi All,

       As it says on the tin, is there any way to determine if a job has been executed as part of a schedule or someone manually invoked the execution?

    And as a little extension to that, any way to link the job to the SSIS package that it kicked off?

    Thanks in advance

    EDIT: We are using 2016

    Alternatively to above, is there a way to get the job id and determine it's next schedule. I do see sysjobschedules but the date doesn't seem reliable - I created a new job/schedule and that had next_run_Date set to 0 and after the first run, it took a while to change to next scheduled date but the Activity Monitor was correctly reflecting the next run so something knows these dates

    • Edited by RyanAB Thursday, September 21, 2017 9:41 PM
    Thursday, September 21, 2017 8:53 PM

All replies

  • SELECT as jobname, CASE ja.run_requested_source WHEN 1 THEN 'SOURCE_SCHEDULER' WHEN 2 THEN 'SOURCE_ALERTER' WHEN 3 THEN 'SOURCE_BOOT' WHEN 4 THEN 'SOURCE_USER' -- This one is manual WHEN 6 THEN 'SOURCE_ON_IDLE_SCHEDULE' ELSE 'UNKNOWN' END AS job_source FROM msdb.dbo.sysjobactivity ja JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC) AND start_execution_date is not null AND stop_execution_date is null;

    Copied from

    Vivek Janakiraman

    Friday, September 22, 2017 12:41 AM
  • Hi Ryan,

    Please refer to following article, see if it is what you want.

    Querying SQL Server Agent Job Information 

    Xi Jin.

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Friday, September 22, 2017 8:54 AM