Verify Whether a SQL Server Agent Job is Running

When I posted an faq about 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
 



Related Articles :

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |