Is this job still executing?

It is easy to use Enterprise Manager to check whether a job is still executing or not.

But I needed to get this information programmatically.

This is a small stored procedure that tells whether a job is running or not.
  @job_name varchar(255),
  @is_job_running int OUTPUT
    DECLARE @job_id uniqueidentifier 
    CREATE TABLE #xp_results 
      job_id uniqueidentifier NOT NULL,
      last_run_date int NOT NULL,
      last_run_time int NOT NULL,
      next_run_date int NOT NULL,
      next_run_time int NOT NULL,
      next_run_schedule_id int NOT NULL,
      requested_to_run int NOT NULL, -- BOOL
      request_source int NOT NULL,
      request_source_id sysname COLLATE database_default NULL,
      running int NOT NULL, -- BOOL
      current_step int NOT NULL,
      current_retry_attempt int NOT NULL,
      job_state int NOT NULL
    SELECT @job_id = sj.job_id 
    FROM msdb.dbo.sysjobs sj
    WHERE sj.name = @job_name
    INSERT INTO #xp_results
    EXEC master.dbo.xp_sqlagent_enum_jobs 1, ''

    SELECT @is_job_running = running
    FROM #xp_results
    WHERE job_id = @job_id
After a few days of struggling I think this is the best way to do it.

No comments:

Post a Comment