Posted by: Karlo Bartels | 2009/05/20

Determine job status

Today I created a stored procedure to find out if a particular SQL Server Job was running or not. I needed some kind of polling mechanism for a web site that enables administrators to start a SQL Server job by simply clicking a button on a web page.

The web page features a AJAX Timer class, which allows you to perform asynchronous postbacks at a defined interval. The timer is enabled when the user clicks the button. The timer code executes the IsJobRunning stored procedure, which returns True if it is currently running, or False if it is not. The timer keeps executing the job at the specified interval. As soon as the job finishes, the web page is updated with the job result and the timer is halted.

So, without further ado, here is the code:

CREATE PROCEDURE [dbo].[IsJobRunning]
(
  
@JobName SYSNAME
)
AS
BEGIN
   SET NOCOUNT ON
;
  
DECLARE @Job SYSNAME;
  
DECLARE @JobIsRunning bit;
      
  
SELECT  @Job = SJ.name
  
FROM    msdb.dbo.sysjobs AS SJ INNER JOIN msdb.dbo.sysjobactivity AS SJA
  
ON      SJ.job_id = SJA.job_id
  
WHERE   SJ.name = @JobName
          
AND SJA.start_execution_date IS NOT NULL
           AND
SJA.stop_execution_date IS NULL;
  
  
IF @Job IS NULL
      
SELECT 0 AS JobIsRunning;
  
ELSE
       SELECT
1 AS JobIsRunning;
      
END

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: