Introduction

Sometimes you find yourself in need of a way to prevent or alter the execution of a query, stored

procedure, or SQL Agent job while some (other) crtitical SQL Agent job is executing. A common use case for this is the situation where the critical SQL Agent job is used to load a data staging table, which will be consumed downstream by targets dependent on a completely guaranteed-consistent view of the data. In this case, it is necessary to wait on the job to complete before accessing the data. One way to accomplish this is to detect the state of the critical SQL Agent job, and either wait or not execute the downstream query or logic.

SQL Agent Job State

SQL Agent job state is a bit of a complicated beast. The msdb database contains a set of tables and views that record job and job step state; however, for a full consistency guarantee that you are absolutely not running your critical downstream query or process until the job is fully completed, committed and/or rolled back, you must also query the sys.dm_exec_sessions to ensure the session is not still active on the instance. This is because it is possible for a failed job to appear as "failed" and thus inactive in msdb-recorded job activity, while the underlying process is still actively rolling back. My gut on this is that there may be other transitory states that act similarly, though admittedly I have no proof of this. Better safe than sorry; so I'm going to check both msdb and sys.dm_exec_sessions when I check the job's state.

Getting Job State From msdb - Easy!

This query will return the msdb.dbo.sysjobs job_id and name of any query currently executing:

SELECT  j.job_id , j.name
FROM    msdb..sysjobs j
WHERE   EXISTS ( SELECT 1
                 FROM   msdb..sysjobsteps sj
                        INNER JOIN msdb..sysjobactivity ja ON ja.job_id = j.job_id
                 WHERE  sj.job_id = j.job_id
                        AND ja.start_execution_date <= GETDATE()
                        AND ja.stop_execution_date IS NULL
                        AND ja.session_id = ( SELECT    MAX(ja2.session_id)
                                              FROM      msdb..sysjobactivity ja2
                                              WHERE     ja2.job_id = j.job_id
                                            ) )

Getting Job State From sys.dm_exec_sessions - Harder!

Checking the same job's state in sys.dm_exec_sessions is a bit harder. SQL Agent jobs appear in sys.dm_exec_sessions with program name values that look like this:

"SQLAgent - TSQL JobStep (Job 0x975430A7DC577B44B949EAAC92123325 : Step 2)"

The hexadecimal number encodes the msdb.dbo.sysjob's job_id value. The hex number always begins at the 32nd character of the sys.dm_exec_sessions program_name value, and is always 32 characters long. We can therefore extract it for running or sleeping processes with:

SELECT  SUBSTRING(sp.[program_name], 32, 32)
FROM    sys.dm_exec_sessions sp
WHERE   sp.Status IN ( 'running', 'sleeping' )

but then we need a hex-to-GUID converter to match it to a job_id value. This is provided by the following custom conversion function:

CREATE FUNCTION [dbo].[uf_HexToChar]
    (
      @binaryValue VARBINARY(100) ,
      @numBytes INT
    )
RETURNS VARCHAR(200)
AS
    BEGIN
        DECLARE @i VARBINARY(10) ,
            @hexdigits CHAR(16) ,
            @s VARCHAR(100) ,
            @h VARCHAR(100) ,
            @currentByte SMALLINT
        SET @hexdigits = '0123456789ABCDEF'
        SET @currentByte = 0
        SET @h = ''
          -- process all  bytes
        WHILE @currentByte < @numBytes
            BEGIN
                SET @currentByte = @currentByte + 1
          -- get first character of byte
                SET @i = SUBSTRING(CAST(@binaryValue AS VARBINARY(100)), @currentByte, 1)
          -- get the first character
                SET @s = CAST(SUBSTRING(@hexdigits, @i % 16 + 1, 1) AS CHAR(1))
          -- shift over one character
                SET @i = @i / 16
          -- get the second character
                SET @s = CAST(SUBSTRING(@hexdigits, @i % 16 + 1, 1) AS CHAR(1))
                    + @s
          -- build string of hex characters
                SET @h = @h + @s
            END
        RETURN(@h)
    END
GO

Now we can join sys.dm_exec_sessions to msdb.dbo.sysjobs and match the two, and return any jobs found in sys.dm_exec_sessions with:

SELECT  j.job_id, j.name
FROM    msdb..sysjobs j
WHERE   dbo.uf_HexToChar(j.job_id, 16) IN (
        SELECT  SUBSTRING(sp.[program_name], 32, 32)
        FROM    sys.dm_exec_sessions sp
        WHERE   sp.status IN ( 'running', 'sleeping' ) )

Hopefully the relatively small number of processes and jobs will minimize the performance issue with the computed-to-computed non-indexed join involved.

Putting It All Together

So now what I want is a function I can call to see if a particular SQL Agent job is running. It will return 0 if the job isn't running, 1 if it is, and NULL if the job doesn't exist or some other bad-input condition occurs.

I want to pass either a SQL Agent job ID GUID, or the job's name (or a uniquely-identifying fragment thereof). For this I can pass a varchar parameter, and if it matches a GUID value I will assume it's a job ID, otherwise that it is part or all of the job name.

Hmm, since I don't have SQL 2012's TRY_CONVERT() function yet, I'll need another helper function to test if the passed string is a GUID or not:

CREATE FUNCTION uf_IsGUID ( @Value VARCHAR(MAX) )
RETURNS BIT
AS
    BEGIN
        DECLARE @rtn BIT
        SET @rtn = 0
        IF @Value IS NULL OR LOWER(@Value) LIKE '[a-f0-9][a-f0-9][a-f0-9][a-f0-9][a-f0-9][a-f0-9][a-f0
-9][a-f0-9]\-[a-f0-9][a-f0-9][a-f0-9][a-f0-9]\-[a-f0-9][a-f0-9][a-f0-9][a-f0-9]\-[a-f0-9][a-f0-9][a-f0
-9][a-f0-9]\-[a-f0-9][a-f0-9][a-f0-9][a-f0-9][a-f0-9][a-f0-9][a-f0-9][a-f0-9][a-f0-9][a-f0-9][a-f0-9]
[a-f0-9]'
            ESCAPE '\'
            SET @rtn = 1
        RETURN @rtn
    END
GO

Note that NULL *is* a valid GUID and can be converted to a UNIQUEIDENTIFIER !

Now, I can put together my prime objective:

CREATE FUNCTION [dbo].[uf_IsSQLAgentJobRunning]
    (
      @SQLAgentJobNameFragmentOrId VARCHAR(128)
    )
RETURNS BIT
AS
    BEGIN
        DECLARE @rtn BIT ,
            @isGUID BIT
        -- assume job is not running; must prove otherwise!
        SET @rtn = 0
        -- scrub inputs
        SET @SQLAgentJobNameFragmentOrId = NULLIF(@SQLAgentJobNameFragmentOrId,
                                                  LTRIM(RTRIM('')))
        -- return NULL if NULL or blanks passed (sanity check)
        IF @SQLAgentJobNameFragmentOrId IS NULL
            RETURN NULL
        -- was a job_id passed ?
        SET @isGUID = dbo.uf_IsGUID(@SQLAgentJobNameFragmentOrId)
        -- if no matching job found by job_id or name, return NULL
        IF 1 <> ( SELECT  COUNT(*)
                        FROM    msdb..sysjobs j WITH ( NOLOCK )
                        WHERE   ( @isGUID = 0
                                  AND j.name LIKE '%'
                                  + @SQLAgentJobNameFragmentOrId + '%'
                                )
                                OR ( @isGUID = 1
                                     AND j.job_id = @SQLAgentJobNameFragmentOrId
                                   ) )
            RETURN NULL
        -- if both msdb and dm_exec_sessions considers the job as "running", return 1
        IF EXISTS ( SELECT  1
                    FROM    msdb..sysjobs j
                    WHERE   dbo.uf_HexToChar(j.job_id, 16) IN (
                            SELECT  SUBSTRING(sp.[program_name], 32, 32)
                            FROM    sys.dm_exec_sessions sp
                            WHERE   sp.Status IN ( 'running', 'sleeping' ) )
                            AND ( ( @isGUID = 0
                                    AND j.name LIKE '%'
                                    + @SQLAgentJobNameFragmentOrId + '%'
                                  )
                                  OR ( @isGUID = 1
                                       AND j.job_id = @SQLAgentJobNameFragmentOrId
                                     )
                                ) )
            AND EXISTS ( SELECT 1
                         FROM   msdb..sysjobs j
                         WHERE  EXISTS ( SELECT 1
                                         FROM   msdb..sysjobsteps sj
                                                INNER JOIN msdb..sysjobactivity ja ON ja.job_id =
j.job_id
                                         WHERE  sj.job_id = j.job_id
                                                AND ja.start_execution_date <= GETDATE()
                                                AND ja.stop_execution_date IS NULL
                                                AND ja.session_id = ( SELECT
                                                              MAX(ja2.session_id)
                                                              FROM
                                                              msdb..sysjobactivity ja2
                                                              WHERE
                                                              ja2.job_id = j.job_id
                                                              ) ) )
            SET @rtn = 1
        RETURN @rtn
    END
GO

Usage

SELECT [dbo].[uf_IsSQLAgentJobRunning]('My Job name here')

returns 1 if 'My Job name here' is running, 0 if it is not.

SELECT [dbo].[uf_IsSQLAgentJobRunning]('FEFB4793-22F1-4744-916C-E672A7D14756')

returns 1 if the job with job_id FEFB4793-22F1-4744-916C-E672A7D14756 is running, 0 if it is not.

Now you can do things like:

WHILE [dbo].[uf_IsSQLAgentJobRunning]('FEFB4793-22F1-4744-916C-E672A7D14756') = 1
BEGIN
   WAITFOR DELAY '00:01:00'
END
EXEC some critical process

or:

SELECT * FROM SomeDataTable WHERE [dbo].[uf_IsSQLAgentJobRunning]('FEFB4793-22F1-4744-916C-E672A7D14756') = 0

or:

WHILE [dbo].[uf_IsSQLAgentJobRunning]('FEFB4793-22F1-4744-916C-E672A7D14756') = 0
BEGIN
   --process row(s) or a batch of data as long as job hasn't restarted
END

or, in an initial job step, wait for one job to complete before beginning another:

WHILE [dbo].[uf_IsSQLAgentJobRunning]('FEFB4793-22F1-4744-916C-E672A7D14756') = 1
BEGIN
   WAITFOR DELAY '00:00:01'
END

or how about a listener job C that has a job step that listens for the end of job A and starts job B or when job A has completed?

WHILE [dbo].[uf_IsSQLAgentJobRunning]('A') = 1
BEGIN
   WAITFOR DELAY '00:00:01'
END
EXEC msdb.dbo.sp_Start_job @job_name='B'

Sure, you could start job B from A directly in a final step in A, but if A fails, depending on the severity level of the error, B might not be started. Ditto if A is cancelled. This gives you a foolproof wait to start job B regardless of the final state of job A.

Disclaimer

The source code is provided to you as is, without warranty. There is no warranty for the program, expressed or implied, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose and non infringement of third party rights. The entire risk as to the quality and performance of the program is with you. Should the program prove defective, you assume the cost of all necessary servicing, repair and correction.