tim laqua dot com Thoughts and Code from Tim Laqua

23May/090

A Cleaner Way to Detect the State of a SQL Server Agent Job In SQL Server 2005

I have always felt that the traditional approach to getting Agent Job status by creating a table and populating it with output from xp_sqlagent_enum_jobs was incredibly crude. So I poked around a bit and came up with a solution using a Table Valued Function that queries sysjobactivity to determine the status of a given job. The reason I use a Table Valued Function is because we have a few tables that contain a list of jobs that need to be started at certain points in a given process (and they may or may not already be running) - with the TVF, you can simply CROSS APPLY the function and bob's your uncle.

Example
Say you needed to run a chunk of code only if a set of jobs in a given table aren't running:

	IF  NOT EXISTS (SELECT 1
		FROM AppJobDependency a WITH(NOLOCK)
		CROSS APPLY [dbo].[Utility_GetAgentJobStatus_F01](a.[JobName]) c
		WHERE [JobStatus] = 1)
	BEGIN
		-- Code goes here
	END

Table Valued Function

/*************************************************************************************
***
*** Procedure:  		[Utility_GetAgentJobStatus_F01]
*** Purpose:		    Get the current status of given agent job
***				
***			
*** Author:		      tl
*** Date Created:	  2009-03-03
*** 
*** Revision History
*** Date		Author			Description
*** 2009-03-03	tl				Created
*************************************************************************************/
CREATE FUNCTION [dbo].[Utility_GetAgentJobStatus_F01] 
(
	@JobName VARCHAR(255)
)
RETURNS @JobStatus TABLE ([JobStatus] BIT)
AS
BEGIN
 
	WITH JobActivity AS
	(
	SELECT 
		 a.start_execution_date
		,a.stop_execution_date
		,ROW_NUMBER() OVER (ORDER BY session_id DESC) AS [RowNumber]
	FROM
		msdb.dbo.sysjobactivity a WITH(NOLOCK)
		INNER JOIN msdb.dbo.sysjobs b WITH(NOLOCK)
		   ON 
			a.[job_id] = b.[job_id]
			AND b.[name] = @JobName
	)
	INSERT INTO @JobStatus
	SELECT COUNT(1)
	FROM JobActivity
	WHERE
		start_execution_date IS NOT NULL
		AND stop_execution_date IS NULL
		AND RowNumber = 1
 
	RETURN
END
Comments (0) Trackbacks (0)

No comments yet.


Leave a comment

No trackbacks yet.