jagoodie
asked on
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.
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.
How about using sp_help_job:
EXEC msdb..sp_help_job @job_name = 'MyJob'
EXEC msdb..sp_help_job @job_name = 'MyJob'
ASKER
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?
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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....
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).
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).
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.