Verify Whether a SQL Server Agent Job 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

Comments

Popular posts from this blog

How to create RSS Feed in Asp.Net

Auto generate change scripts in SQL Server Management Studio SSMS for tables

HOW TO Use the NumericUpDown Control