Detect if a Job is running using TSQL

Is it possible to detect if a SQL Server Agent job is running using TSQL? I would like to programmatically determine if a job is running and take certain action if it is or isn't.

Thanks - Amos.

One simple way is to query the msdb.dbo.sysjobactivity table for the job and check to see if stop_execution_date is null. If it is, the job is running. At least that's my understanding.

sp_help_job returns a current_execution_status column. The problem with using this stored procedure is that it cannot be used in an insert...exec statement, because it has an insert...exec statement in it and these cannot be nested. You could go into the procedure and find the procedure it runs to get the job status, but that procedure is not documented.

Ron Rice

|||

Using Books Online, look up sp_help_job.

For example,

EXECUTE sp_help_job
@.job_id = @.MyJobID [OR] @.job_name = @.MyJobName,

@.execution_status > 0