How to determine if a sql server job is already executing (sp_help_job)

• Steve-3_5_7_9

  SSChampion

  Points: 10832

  More actions

  January 13, 2005 at 6:52 am

  #109360

  I need to find out if a job is running by checking the results of [current_execution_status] through transact sql using this system stored proc: 

  sp_help_job

  @job_name = 'test',

  @execution_status = 1,

  @job_aspect = 'JOB'

  I would like to check the results of [current_execution_status] through transact sql.  If it isn't running then I want to start the job [sp_start_job].  This system stored procedure doesn't have output parameters, so I can't check the values.  Is is possible to use this procedure [sp_help_job] and query the results using transact sql?

  I could run this alternative query

  SELECT instance_id, FROM msdb.dbo.sysjobhistory WHERE job_id = 'abc123'

  and run_status = 1

  and use max(instance_id), but I'm not sure if this is the correct value I am looking for.

  Thanks

  Steve

• Steve-3_5_7_9

  SSChampion

  Points: 10832

  More actions

  January 14, 2005 at 5:19 am

  #537442

  I'll give it a try.  Thanks