Monday 28 April 2014

SQL Server: Dynamically Check if SQL Agent Job is Running

It can be helpful to have a SQL script to determine if a SQL Agent job is currently running or not. In SSMS this check can be done using the tools and GUIs provided, but it may be necessary to embed this check in script.  For example you may have a series of scripts, some scheduled through jobs and some triggered by, say, a button click on an interface.  If you need to ensure that the button job does not run at the same time as the Agent Job, you'll need to dynamically check the agent job status. Here's how to do it.

First let's create a simple SQL Agent job with two steps:



In the above image I have created a new job called TestDelay, containing two steps, WAIT 1 and WAIT 2.  The first step simply waits for 20 seconds before completing successfully, while the second step waits for a further 5 seconds before doing the same.

The information relating to this job and it's execution is contained in the following tables in the msdb database:
dbo.sysjobs
dbo.sysjobsteps
dbo.sysjobactivity

From the first table we can find out the job_id, which is key for joining the 3 tables together:

SELECT job_id FROM msdb.dbo.sysjobs

WHERE name LIKE 'TestDelay'

Then from the second table we can find out "step" information:


SELECT step_id, step_name, subsystem
FROM msdb.dbo.sysjobsteps
WHERE job_id = (SELECT job_id FROM MSDB.dbo.sysjobs

                        WHERE name LIKE 'TestDelay')


step_id     step_name     subsystem
----------- ---------------------------
1           WAIT 1        TSQL
2           WAIT 2        TSQL

(2 row(s) affected)

Here we can see the two steps in our job and their step_ids. Using the third table we can run the following query for our job:

SELECT     start_execution_date,
           last_executed_step_id,
           last_executed_step_date

FROM       msdb.dbo.sysjobactivity a
INNER JOIN msdb.dbo.sysjobs j
ON   j.job_id = a.job_id

WHERE      j.name LIKE 'TestDelay'

start_execution_date    last_executed_step_id last_executed_step_date
----------------------- --------------------- -----------------------
2014-04-28 15:20:23.000 2                     2014-04-28 15:20:43.000

(1 row(s) affected)


We can see that on it's most recent execution, the job finished it's second and final step at 15:20:43. If the job were still running and had not completed even the first step yet,  then the last_executed_step_id and last_executed_step_date fields would be null for this row.
If, however, the first step has completed but the second has not, the two fields will be populated but the step_id will be 1, not 2.

So to know if our job is still running we really need to combine these three tables into a single query, checking whether the last_execution_step_id field is null, and if not, if it is at it's maximum value or not.

SELECT DISTINCT
            start_execution_date,
            last_executed_step_id,
            last_executed_step_date,
CASE  WHEN  last_executed_step_id = MAX(step_id) THEN 'Job Complete'    
      ELSE  'Job Still Running'
END         AS result
FROM        msdb.dbo.sysjobactivity a
INNER JOIN  msdb.dbo.sysjobsteps s
      ON    a.job_id=s.job_id
INNER JOIN  msdb.dbo.sysjobs j
      ON    j.job_id = a.job_id
WHERE       j.name LIKE 'TestDelay'
GROUP BY    start_execution_date,
            last_executed_step_id,

            last_executed_step_date

In the above query, the "result" field will say either "Job Still Running" or "Job Complete" depending on the logic stated above.  Alternatively the script could be rewritten in the form of a stored procedure that accepts a parameter containing the job name and returns a 1/0 depending on whether it is running or not.

It is worth examining the rest of the fields in these three tables as they can offer additional options for extracting information about the jobs set up on the server.

1 comment: