Querying agent job status, executing and waiting for job completion from within T-SQL

In this article I would like to describe how easily you can query an Agent Job status from within T-SQL command and also how it is possible to execute an agent job and wait for it’s completion.

It can happen, that you do not have access to the Job Activity Monitor and would like to check the status of particular job or simply you would like to query the status to know whether the job finished or not.

For this purposes you can crate a very simple function in the [msdb].

USE [msdb]
GO
-- ============================================= 
-- Author:      Pavel Pawlowski 
-- Create date: 2009/08/14 
-- Description: Gets state of particular Job 
-- 
-- -2 = Job was not Found 
-- -1 = Job is Disabled 
--  0 = Failed 
--  1 = Succeeded 
--  2 = Retry 
--  3 = Canceled 
--  4 = In progress 
--  5 = Disabled 
--  6 = Idle
-- ============================================= 
CREATE FUNCTION [dbo].[fn_GetJobStatus] ( 
    @pJobName varchar(100) 
) 
RETURNS int 
AS 
BEGIN 
    DECLARE @status int     

    SELECT 
        @status = CASE 
            WHEN O.enabled = 0 THEN -1
            WHEN OA.run_requested_date IS NULL THEN 6
            ELSE ISNULL(JH.RUN_STATUS, 4)
        END        
    FROM MSDB.DBO.SYSJOBS O 
    INNER JOIN MSDB.DBO.SYSJOBACTIVITY OA ON (O.job_id = OA.job_id) 
    INNER JOIN (SELECT MAX(SESSION_ID) AS SESSION_ID FROM MSDB.DBO.SYSSESSIONS ) AS S ON (OA.session_ID = S.SESSION_ID) 
    LEFT JOIN MSDB.DBO.SYSJOBHISTORY JH ON (OA.job_history_id = JH.instance_id) 
    WHERE O.name = @pJobName 

    RETURN ISNULL(@status, -2) 
END 
GO

This function queries several tables from [msdb] to retrieve the status and takes a job name as parameter. Return codes are described in the T-SQL above and codes 2 and 4 are representing a job, which is currently running. Where 2 represents job, which is running in retry mode after previous failure.

This function we can use for writing a procedure, which will allow us to wait for it’s completion.

USE [msdb]
GO
-- ============================================= 
-- Author:      Pavel Pawlowski 
-- Create date: 2009/08/14 
-- Description: Waits for a Job Completion 
--
-- @pStatusRequestFrequence defines delay between requesting status of the Job in seconds. 
--
--Returns values: 
-- -2 = Job was not Found 
-- -1 = Job is Disabled 
--  0 = Failed 
--  1 = Succeeded 
--  2 = Retry 
--  3 = Canceled 
--  4 = In progress 
--  5 = Disabled
--  6 = Idle
-- ============================================= 
CREATE PROCEDURE [dbo].[usp_WaitForJob]  
    @pJobName varchar(100),  
    @pStatusRequestFrequency tinyint = 5,
    @pWaitInitialIdle bit = 1
AS 
BEGIN 
    SET NOCOUNT ON; 
    DECLARE @hours int 
    DECLARE @mins int 
    DECLARE @delay varchar(8) 
    DECLARE @status int 

    SET @hours = ROUND(@pStatusRequestFrequency / 3600, 0, 1) 
    SET @pStatusRequestFrequency = @pStatusRequestFrequency - (@hours * 3600) 
    SET @mins = ROUND(@pStatusRequestFrequency / 60, 0, 1) 
    SET @pStatusRequestFrequency = @pStatusRequestFrequency - (@mins * 60) 

    SET @delay = RIGHT('00' + CONVERT(varchar(2), @hours), 2) + ':' + RIGHT('00' + CONVERT(varchar(2), @mins), 2) + ':' + RIGHT('00' + CONVERT(varchar(2), @pStatusRequestFrequency), 2)

    SET @status = 4 
    WHILE (@status IN (2, 4)) 
    BEGIN 
        SET @status = dbo.fn_GetJobStatus(@pJobName) 
        IF (@status IN (2, 4) OR (@status = 6 AND @pWaitInitialIdle = 1) )
        BEGIN
            WAITFOR DELAY @delay
            SET @pWaitInitialIdle = 0
        END
    END 

    RETURN @status 
END

The procedure executes periodically the above function to get execution status of the job. and when it detects that it is not running, it returns the status of the job. The procedure takes again a job name as parameter and additional two parameters. One to specify the delays between the check for the execution status (by default 5) and an argument specifying whether wait for completion even querying for the first time and the first return value was “Idle”. It can happen, when you start the job and immediately query status, that the state is not reflected in the [msdb] system tables.

Finally at the end we can create a stored procedure for executing the job itself. It will use the previous stored procedure for waiting and the function for querying the status prior execution of the job.

-- ============================================= 
-- Author:      Pavel Pawlowski 
-- Create date: 2009/08/14 
-- Description: Runs Particular Job 
-- Start Options: 
--  0 = if job is already running, do not run job and finish 
--  1 = if job is already running, wait for completion and then run it again 

-- Return Options: 
--  0 = Start Job and wait for job completion 
--  1 = Start Job and return from procedure 

--Returns values: 
-- -2 = Job was not Found 
-- -1 = Job is Disabled 
--  0 = Failed 
--  1 = Succeeded 
--  2 = Retry 
--  3 = Canceled 
--  4 = In progress 
--  5 = Disabled
--  6 = Idle

-- @pStatusRequestFrequence defines delay between requesting status of the Job in seconds. 
-- ============================================= 
CREATE PROCEDURE [dbo].[usp_RunJob]  
    @pJobName varchar(100),  
    @pStartOption int = 0, 
    @pReturnOption int = 0, 
    @pStatusRequestFrequency tinyint = 5
AS 
BEGIN 
    SET NOCOUNT ON; 
    DECLARE @status int 

    SET @status = dbo.fn_GetJobStatus(@pJobName) 

    IF (@status IN (2, 4) AND @pStartOption = 1) 
        EXEC @status = dbo.usp_WaitForJob @pJobName, @pStatusRequestFrequency 

    IF (@status IN (0, 1, 3, 6)) 
    BEGIN 
        EXEC MSDB.dbo.sp_start_job @pJobName 

        IF (@pReturnOption <> 1) 
            EXEC @status = dbo.usp_WaitForJob @pJobName, @pStatusRequestFrequency 
        ELSE 
            SET @status = dbo.fn_GetJobStatus(@pJobName) 
    END 

    RETURN @status 
END

This procedure again takes several parameter including the job name to be executed. It has also option whether to wait for the job completion or not and how to handle situation if the job is already running when this procedure is executed.

You can use this mechanism also e.g. for executing Integration Services Package from within T-SQL. You only need to create a job for that package, which will have no schedule. Then using the usp_RunJob you start the job, which takes care about execution of the package itself.