3/17/2012

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

No comments:

Post a Comment