Q:

How can I log the way in which a job was started?

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?

A:

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

