Check SQL Agent Job Status

MyDoggieJessie, 2012-01-05 (first published: 2011-12-09)

These functions are especially useful when you have to check the status of the agent jobs running across multiple servers (they will all have different GUIDS).

The first function looks up the agent job id from the sysjobs table. The second function looks up the job status from the sysjobactivity table and returns either one of the following 7 results:

0 - Not executing

1 - Current executing

2 - Waiting for thread

3 - Between retries

4 - Faileure during last run

5 - Suspended

6 - (not used)

7 - Performing completion actions

NULL - Job does not exists

So by easily passing in an agent job name, you can get the current executing status on any given server.

CREATE FUNCTION [dbo].[fx_FetchSQLAgentJobID] ( @JobName char(250) ) RETURNS uniqueidentifier AS /* ----------------------------------------------------------------------------------------------------------------------------------- Purpose: Returns the job_id for the @job_id parameter of the msdb.dbo.sp_start_job procedure Created On: 06/13/2011 Create By: MyDoggiejessie ----------------------------------------------------------------------------------------------------------------------------------- SELECT dbo.fx_FetchSQLAgentJobID('YourJob') */BEGIN RETURN ( (SELECT job_id from msdb.dbo.sysjobs WHERE name = @JobName ) ) END CREATE FUNCTION [dbo].[fx_FetchJobStatus] ( @JobID uniqueidentifier ) RETURNS int AS /* ----------------------------------------------------------------------------------------------------------------------------------- Purpose: Returns the status of a given SQL Agent Job - this becomes useful when checking the status of a job before another can be run Created On: 06/13/2011 Create By: MyDoggiejessie ----------------------------------------------------------------------------------------------------------------------------------- SELECT dbo.fx_FetchJobStatus(dbo.fx_FetchSQLAgentJobID('YourJob')) */BEGIN RETURN ( (SELECT TOP 1 ISNULL(last_executed_step_id, 0) FROM msdb.dbo.sysjobactivity WHERE job_id = @JobID ORDER BY run_requested_date DESC ) ) END