Using SQL Server Agent, I've created a Job that runs some SQL. This SQL does some work on one database, then adds a row to a logging table on a separate database.

The Job is run on a couple of schedules and is also occasionally started by hand.

On the log row, I'd like to be able to record what it was that started the Job. In other words, I'd like to detect whether the Job was started by schedule 1, schedule 2, or ad hoc.

Is this possible? If so, how?


Here is script to get running jobs status along with Schedule information

SELECT  sj.name JobName,
        sj.start_step_id StartStepID,
        s.name JobScheduleName
FROM    msdb.dbo.sysjobactivity AS sja
        INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
        INNER JOIN msdb.dbo.sysjobschedules AS js ON js.job_id = sj.job_id
        INNER JOIN msdb.dbo.sysschedules AS s ON js.schedule_id = s.schedule_id
WHERE   sja.start_execution_date IS NOT NULL
        AND sja.stop_execution_date IS NULL
  • Thanks, but I can't get this to return any rows. I've changed it (pastebin.com/m4wibgzh) to insert rows into a log table, but when I run it in a job it doesn't actually do anything. Am I missing something?
    – Tom Wright
    Oct 1 '14 at 11:36
  • is your job currently is in Running status? if not then replace where clause of script with " WHERE sj.name='Job Name' "
    – AA.SC
    Oct 1 '14 at 11:41
  • Yes, I put my modified version of your SQL into one of the job steps. When I ran the job, no rows were inserted into my logging table. Since the SQL would have been executed while the job was running, I would have expected it to have inserted one row.
    – Tom Wright
    Oct 1 '14 at 11:46
  • Tom, 1. Execute script without any where clause 2. Verify rows is there any row exist according to your requirement. 3. apply required filters accordingly. 4. add script to any job
    – AA.SC
    Oct 1 '14 at 11:54
  • Fixed it! Needed to INNER JOIN the tables pertaining to schedules, as jobs started out of a schedule will otherwise not be returned. I also then added an ISNULL() to s.name to handle these job instances.
    – Tom Wright
    Oct 1 '14 at 12:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.