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.
