Showing posts with label SQL Server. Show all posts
Showing posts with label SQL Server. Show all posts

Tuesday, July 31, 2012

Is Agent Job Running???

CREATE PROC portal.isJobRunning (@jobName VARCHAR (256))
AS
BEGIN

/**************************************************************************
Joe Kelly
2012-07-31 17:56:04.540

Determines if a job is running or not

EXEC portal.isJobRunning @jobName = 'Export%'

Template source:
http://weblogs.asp.net/johnbilliris/archive/2009/10/13/how-to-determine-if-a-sql-job-is-running.aspx

**************************************************************************/
SET NOCOUNT ON

DECLARE @xp_results TABLE (
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, 'dbo'

SELECT xpr.running
, sjv.name
FROM @xp_results xpr
JOIN MSDB.dbo.sysjobs_view sjv
ON xpr.job_id = sjv.job_id
WHERE sjv.name LIKE @jobName

END