Microsoft SQL Server Agent Job: Get Schedule that triggered the Job

Question

I have SQL Server Agent Job on my System that copies data into at table for later evaluation purposes. The Job runs on two types of schedules every Friday every week and last day of the month. The target data records should also contain a column indicating the schedule that originally triggered the job. But I found no way so far to receive this data as parameter or so. I'm using a Microsoft SQL Server 2017.

I did a web search but maybe searched for the wrong keywords. I also thought about comparing current time to expected runtime per schedule but that seemed to be not a fault tolerant option to me.

I like to fill a column "schedule" with values like "End of week", "End of month"

Answer 1

After long search and analyzing I finally found a solution that at least fit my needs: The undocumented and unsupport stored procedures provides the schedule that triggered a job ind Column Request Source ID:

EXEC master.dbo.xp_sqlagent_enum_jobs 1, garbage

see also: https://am2.co/2016/02/xp_sqlagent_enum_jobs_alt/

Answer 2

sys tables are your friend here. Documentation

• sysjobs has your job information.

• sysjobschedules links your job to its schedule.

• sysschedules has your schedule info.

  SELECT j.*
       , s.*
    FROM sysjobs j
    JOIN sysjobschedules js ON j.id = js.job_id
    JOIN sysschedules     s ON js.schedule_id = s.schedule_id
   WHERE j.name = 'your job name here'