Solved

Check Job Status

Posted on 2001-09-06
8
1,048 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:jagoodie
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6462184
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.
0
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 6463558
How about using sp_help_job:

EXEC msdb..sp_help_job @job_name = 'MyJob'
0
 
LVL 2

Author Comment

by:jagoodie
ID: 6464218
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?
0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6464482
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.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 4

Accepted Solution

by:
Jeremy_D earned 100 total points
ID: 6464647
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
*/
     SET NOCOUNT ON

     DECLARE @job_id UNIQUEIDENTIFIER,
          @retval INT

     EXECUTE @retval = msdb..sp_verify_job_identifiers '@job_name',
                                     '@job_id',
                                     @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)
     END

     CREATE TABLE #results (
          [Job ID] UNIQUEIDENTIFIER,
          [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
GO



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.

Cheers,
Jeremy

0
 
LVL 2

Author Comment

by:jagoodie
ID: 6464699
You are a prince among men.  Very nice work.
0
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 6479488
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....
0
 

Expert Comment

by:yetimonster
ID: 8782057
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).
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now