Verify Whether a SQL Server Agent Job is Running

Finding Running SQL Server Agent Jobs I had several few emails and queries on how verify whether a job is running by using only a TSQL script. The below script will verify if the job exists and whether or not it is running.

DECLARE @jobname sysname  ='Running Job' -- Enter the job name here
SET NOCOUNT ON
IF NOT EXISTS (SELECT * FROM msdb..sysjobs Where Name = @jobname)
BEGIN
	PRINT 'Job does not exists'
END
ELSE
BEGIN
	CREATE TABLE #xp_results
	(
	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
	)
	INSERT INTO  #xp_results
	EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'sa'
	IF EXISTS (
	SELECT 1 FROM #xp_results X
	INNER JOIN
	msdb..sysjobs J ON X.job_id = J.job_id
	WHERE x.running = 1 AND j.name = @jobname)
	BEGIN
	 Print 'Job is Running'
	END
	ELSE
	BEGIN
	Print 'Job is not Running'
	END
	DROP TABLE #xp_results
END
 
]]>

Leave a comment

Your email address will not be published.