Check SQL Agent Job Status

  • 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

  • The function is only returning the ID of the last step executed, not the status of the job.

  • 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

  • The second function uses the guid to fetch last_executed_step_id

  • 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

  • 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?

  • 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

  • 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.

  • 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

  • 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.

  • Thanks for the script.

  • Never argue with an idiot!

    The script does not work. You can't argue with that!

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply