Get SQL Agent job status

I was working on a script to get the agent job status so i can trigger a necessary action based on the job status. So I thought I I will share that here

Example here I have two sql agent jobs these are simple jobs

sql ajent job 1

Here is the procedure to start with

USE [msdb]
GO

/****** Object:  StoredProcedure [dbo].[SpGetJobStatus]    Script Date: 11/20/2014 9:25:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[SpGetJobStatus] (
	@Jobname SYSNAME
	,@state INT OUTPUT
	)
AS
DECLARE @job_owner SYSNAME
DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @RowCount INT
DECLARE @Jobs AS TABLE (
	RowCounter INT IDENTITY(1, 1)
	,job_id UNIQUEIDENTIFIER
	,job_owner SYSNAME
	,Jobname SYSNAME
	)

INSERT INTO @Jobs (
	job_id
	,job_owner
	,Jobname
	)
SELECT job_id
	,sl.NAME
	,sj.NAME
FROM msdb.dbo.sysjobs sj
INNER JOIN sys.syslogins sl ON sj.owner_sid = sl.sid
WHERE sj.NAME = @Jobname

--Or @Jobname IS NULL
SET @RowCount = @@ROWCOUNT

DECLARE @xp_results TABLE (
	job_id UNIQUEIDENTIFIER NOT NULL
	,last_run_date INT NOT NULL
	,last_run_time INT NOT NULL
	,next_run_date INT NOT NULL
	,next_run_time INT NOT NULL
	,next_run_schedule_id INT NOT NULL
	,requested_to_run INT NOT NULL
	,-- BOOL  
	request_source INT NOT NULL
	,request_source_id SYSNAME COLLATE database_default NULL
	,running INT NOT NULL
	,-- BOOL  
	current_step INT NOT NULL
	,current_retry_attempt INT NOT NULL
	,job_state INT NOT NULL
	)

WHILE @ROWCOUNT > 0
BEGIN
	INSERT INTO @xp_results
	EXECUTE master.dbo.xp_sqlagent_enum_jobs 1
		,@job_owner
		,@job_id

	SET @RowCount = @RowCount - 1
END

SET @state = (
		SELECT job_state
		FROM @xp_results results
		INNER JOIN msdb.dbo.sysjobs sj ON results.job_id = sj.job_id
		WHERE sj.NAME = @Jobname
		)

SELECT sj.NAME
	,job_state
	,CASE job_state
		WHEN 1
			THEN 'Executing'
		WHEN 2
			THEN 'Waiting for thread'
		WHEN 3
			THEN 'Between retries'
		WHEN 4
			THEN 'Idle'
		WHEN 5
			THEN 'Suspended'
		WHEN 6
			THEN '<unknown>'
		WHEN 7
			THEN 'Performing completion actions'
		ELSE ''
		END job_state_description
FROM @xp_results results
INNER JOIN msdb.dbo.sysjobs sj ON results.job_id = sj.job_id
WHERE sj.NAME = @Jobname

RETURN @state

Procedure has one input parameter and one output parameter

Input parameter is the job name and output parameter will provide us the job state

agent job status

EXEC [dbo].[SpGetJobStatus] ‘job1’,null

Below script will help us get the job state to a local variable

DECLARE @jobstate INT 

EXEC [dbo].[SpGetJobStatus] 'job1',@state = @jobstate OUTPUT

SELECT @jobstate AS jobstate 

Here is the script that polls on above procedure to get the job state, while loop breaks only when job becomes idle

DECLARE @jobstate INT

EXEC [dbo].[SpGetJobStatus] 'job1'
	,@state = @jobstate OUTPUT

WHILE (@jobstate != 4)
BEGIN
	EXEC [dbo].[SpGetJobStatus] 'job1'
		,@state = @jobstate OUTPUT

	WAITFOR DELAY '00:00:05'
END

SELECT @jobstate
	-- Do something job has finished 
	-- job1 is idle start another job 

We can use above step before start first step in second job as seen below, when second job runs it waits until job 1 is finished

agent job step

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s