January 4, 2012 at 11:39 pm
Comments posted to this topic are about the item Check SQL Agent Job Status
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 5, 2012 at 2:40 am
The function is only returning the ID of the last step executed, not the status of the job.
January 5, 2012 at 7:29 am
The two functions need to be use together. One returns the guid from the provided name, the other uses the guid to fetch the status
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 5, 2012 at 7:51 am
The second function uses the guid to fetch last_executed_step_id
January 5, 2012 at 8:21 am
Correct. I wrote this to monitor job status (instead of using the xp_sqlagent_enum_jobs method - as that results in having to insert the result set into a temp table, then select from it). Using the functions listed in this article avoids this and is very effective - It is used in literally hundreds of places at our company and hasn't let me down yet.
In addition, using the xp_sqlagent_enum_jobs means you have to manually (hard-code) the @job_id parameter, which isn't very useful in a stored-procedure/Agent job. Using a function to pass in the job name to look up the GUID is a little more smooth.
Test it out!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 5, 2012 at 8:35 am
Thanks for the article - I can see that this is useful.
We are only now starting to use SSIS and monitoring job status would be useful to me for my daily jobs.
But I'm being somewhat pedantic.
The function is named as returning the job status but is in fact only returning the last executed job step ID.
How is that job step ID used to return the job status?
January 5, 2012 at 9:29 am
But I'm being somewhat pedantic.
The function is named as returning the job status but is in fact only returning the last executed job step ID.
How is that job step ID used to return the job status?
When any job runs, using this these functions together will return 1, meaning it's currently executing. When you pass the job name into the fx_FetchSQLAgentJobID function you get the GUID. Passing that GUID into the fx_FetchJobStatus function will return the most recent status that's been logged to the msdb.dbo.sysjobactivity table
SELECT TOP 1 ISNULL(last_executed_step_id, 0)
FROM msdb.dbo.sysjobactivity
WHERE job_id = @JobID
ORDER BY run_requested_date DESC
The most practical uses for these functions are obviously for 0,1 - ex:
IF ((SELECT F1Settings.dbo.fx_FetchJobStatus(F1Settings.dbo.fx_FetchSQLAgentJobID(@job_1))) = 1)
BEGIN
EXEC msdb.dbo.sp_start_job @job_10
END
0 - Not executing
1 - Current executing
2 - Waiting for thread
3 - Between retries
4 - Failure during last run
5 - Suspended
6 - (not used)
7 - Performing completion actions
NULL - Job does not exists
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 18, 2012 at 4:59 pm
I am also trying to see if I could use these function. Using them together, simply returns the last step_id run for a particular job. I have jobs with as many as 8 steps. Hence, I would get an 8 returned. I am not sure how I would be able to get the job status numbers of 0 -7 mentioned that would be returned.
January 19, 2012 at 9:22 am
When the job is running actually running it shows the numbers above, after a step has actually completed it records the step number to the last_executed_step_id column in the msdb.dbo.sysjobactivity table. So if your job did finish successfully it would have an "8" in there.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 16, 2015 at 3:59 pm
sorry this does not do what it is advertised. I'm looking for something very simple (one would think) that returns 1 if job is running RIGHT NOW, 0 if not. (we need to do some things but have to wait for a job to finish, so need to check). This does not do that, it returns the last executed step of a job. Which is NOT "Check SQL Agent Job Status." I'll keep looking.
May 11, 2016 at 7:15 am
Thanks for the script.
June 17, 2016 at 3:31 am
Never argue with an idiot!
The script does not work. You can't argue with that!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply