how to know status of currently running jobs

Question

I need to know if a given Job is currently running on Ms SQL 2008 server. So as to not to invoke same job again that may lead to concurrency issues.

Answer 1

It looks like you can use msdb.dbo.sysjobactivity, checking for a record with a non-null start_execution_date and a null stop_execution_date, meaning the job was started, but has not yet completed.

This would give you currently running jobs:

SELECT sj.name    , sja.* FROM msdb.dbo.sysjobactivity AS sja INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id WHERE sja.start_execution_date IS NOT NULL    AND sja.stop_execution_date IS NULL 

Answer 2

I found a better answer by Kenneth Fisher. The following query returns only currently running jobs:

SELECT     ja.job_id,     j.name AS job_name,     ja.start_execution_date,           ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,     Js.step_name FROM msdb.dbo.sysjobactivity ja  LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id JOIN msdb.dbo.sysjobsteps js     ON ja.job_id = js.job_id     AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_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; 

You can get more information about a job by adding more columns from msdb.dbo.sysjobactivity table in select clause.