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].

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
-- =============================================
-- 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.

http://www.pawlowski.cz/2011/04/querying-agent-job-status-executing-waiting-job-completion-t-sql/

This entry was posted in SQL Server and tagged . Bookmark the permalink.