Check Job Status

I have a job that runs every hour and every once in a while it hangs on the first step and it needs to be stopped and restarted manually.  (it failes because it is connecting to a crappy oracle server that isn't always avaiable i beleive).
Is there a way to check the status of a job?  
I could make another job that runs like 10 mins after that job and if it is running, stop it, and try again.
Who is Participating?
Jeremy_DConnect With a Mentor Commented:
Did some reverse engineering on the msdb stored procs and came up with this. Note that I'm working on 7.0. If you have 2K you'll have to test yourself if it still works, as these stored procs are largely undocumented.

CREATE PROCEDURE dba_sp_GetJobStatus (@job_name sysname) AS
/* Legend of Return values:
   0 = Error in Stored Proc,
   1 = Executing,
   2 = Waiting For Thread,
   3 = Between Retries,
   4 = Idle,
   5 = Suspended,
   6 = [obsolete],
   7 = PerformingCompletionActions

          @retval INT

     EXECUTE @retval = msdb..sp_verify_job_identifiers '@job_name',
                                     @job_name OUTPUT,
                                     @job_id   OUTPUT
     IF (@retval <> 0) BEGIN
          RAISERROR ('Failure in sp_verify_job_identifiers, @job_name is probably non-existant.', 16, 1)
          RETURN (0)

     CREATE TABLE #results (
          [Last Run Date] int,
          [Last Run Time] int,
          [Next Run Date] int,
          [Next Run Time] int,
          [Next Run Schedule ID] int,
          [Requested To Run] int,
          [Request Source] int,
/* NOTE:
   I'm not sure what the datatype for [Request Source ID] must be,
   as it turns out to be NULL every time I tried. Made it a wide
   varchar since most datatypes will be implicitly converted
   to that without problems. If you get datatype conversion
   errors when using this sp, change it to the correct datatype
   (which you should be able to get from the error message)
          [Request Source ID] varchar(255),
          Running int,
          [Current Step] int,
          [Current Retry Attempt] int,
          State int

     INSERT INTO #results EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, ''
     SELECT @retval = State FROM #results WHERE [Job ID] = @job_id
     DROP TABLE #results

     RETURN @retval

Usage is like this:

DECLARE @status int
EXECUTE @status = dba_sp_GetJobStatus 'MyJobName'
PRINT @status

It should be easy to modify so that you can enter a Job ID instead of the name, at this point I have the Job ID returned by sp_verify_job_identifiers which seems to work AOK, but again, is undocumented.


Easiest way is to put an entry in a log table with getdate(), @@spid and logon_time from sysprocesses for that spid when the job starts and finishes. Have a task which checks this table - if there is a start entry and no stop entry and the spid exists with the same logon_time then you know that the job is still running and how long for so can stop it if necessary.
It also gives you a history of the job.

You could also do it by adding another step at the begining which does nothing, look at sysjobhistory - there will be an entry for the new first step but not the next which indicates that the second step has not yet finished.
How about using sp_help_job:

EXEC msdb..sp_help_job @job_name = 'MyJob'
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

jagoodieAuthor Commented:
okay, i see that @execution_status is there, but I don't know how to retreive it, or check its value in a task.
can you assist?
You give it a value to limit the jobs the sp returns.

The sp returns several result sets which you will have trouble interpreting automatically.
Also does it return info about when a job started if it is still running?
The entry is not put in sysjobhistory until a step finishes (which is why I say add a dummy step) and most of these things get info from there.
You will have to check if this gives any info about the start of a running job.
jagoodieAuthor Commented:
You are a prince among men.  Very nice work.
A little update: I just found out that the [Request Source ID] column in the output of xp_sqlagent_enum_jobs should be a sysname type. So it shouldn't be a problem to leave it a varchar(255), but you could change it to a sysname column for aesthetic reasons :)

Just so you know....
Thanks for the code - I had exactly the same problem as jagoodie.

Info on Jeremy_D's extra code; best to leave the [Request Source ID] column as varchar(255) otherwise you get an Insert null error.

In terms of what you would see in your job, I set Step1 to be the following:

DECLARE @status int
EXECUTE @status = OMF_sp_GetJobStatus 'myJobname'
-- Ensure that job status is IDLE
IF NOT @status = 4 raiserror ('Job cannot be started as not in IDLE state.', 16,@status)

The Step ID's should be set as the following "On Success: Goto Next Step", "On Failure: Quit with success"  (unless you really WANT to know every time that the job is running at the same time).
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.