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.

Reporting Services Report – Changing Column Names, Changing Table Names and sql PIVOT

The other day I was creating some SSRS reports.  For each datafeed in an ETL process the rejected rows were being diverted into error tables – a single error table for each feed, eg error_feed1, error_feed2.  As each of the feeds were different, so too were the column names and metadata of the error tables.  To allow users to review and correct these records I needed to build Reporting Services reports on each of the tables.

Initially it looked like I would need a different report for each feed – when you bind a SSRS report object  to a dataset the column names of the dataset have to remain constant or the report will fail, hence one report, one error table.  As I was dealing with dozens of feeds, the prospect of dozens of very similar reports did not seem favourable.

All these reports would be almost identical, the only difference was the column names and the table names.  I was sure there must be an easier way.  I googled around and found several useful suggestions.  Generally they followed the idea of pivoting the columns into rows in the dataset and then using a SSRS matrix object.  EG:

error_feed1

Id	Col1	Col2	Col3	Col4
1	W	X	Y	Z

Would become:

ID	measure	value
1	Col1	W
1	Col2	X
1	Col3	Y
1	Col4	Z

Using a matrix you would put the ID column on the rows, the measure column on the cross tab section, and value in the data section of the matrix object.  Now it wouldn’t matter if the column names changed, if new columns were added or old ones removed from the source table.  The 3 columns outputted by the pivot query would remain and the matrix report will adapt accordingly. The pivot/unpivot command to do the above would look like this:

SELECT ID, measure, value
FROM( SELECT id, col1, col2, col3, col4, col5 FROM error_feed1) p
UNPIVOT( VALUE FOR measure IN (col1, col2, col3, col4, col5)) AS unpvt

The columns are now dynamic, which solves half the problem. But the FROM clause uses specific column and table names, meaning this metatdata needs to be known in advance and hardcoded into the SSRS dataset query.  Which brings us back to our original problem – we cannot hard code these values because they are constantly changing.

In order to get around this problem I decided to have the dataset be the result of a stored procedure.  I can then have greater flexibility in manipulating the data, so long as I return a result set to SSRS at the end, and always with the same column names returned.

The proc accepts one parameter  - the feed name, to be supplied by the user running the report using a standard SSRS drop down.

The proc itself makes use of the sysobjects and syscolumns system tables to get the full list of columns for any given table:

SELECT      c.name
FROM        sys.columns c
INNER JOIN  sys.objects o
      ON    c.object_id = o.object_id
WHERE       type = 'U'
      AND   o.name LIKE @TableNameORDER BY    c.column_id

name
------------
id
col1
col2
col3
col4

Once the table name has been supplied (as a parameter in the SSRS report), dynamic sql can be leveraged to query the system tables and use the results to build a string containing the required sql PIVOT command, with all the relevant column names for any given table.
 

Once the string variable is populated with the sql script it is then executed,  returning a result set of just 3 columns; the same 3 columns - ID, measure and value -  regardless of the table being queried.  The actual code code of the proc is below:

This result set is all that the SSRS queryset would ever see, and the column names would always be the same 3 named columns, regardless of the feed selected by the user.  Setting up the SSRS matrix object in the manner suggested above would then display the contents of the table as normal – effectively doing a PIVOT to counter the UNPIVOT done in the stored procedure.

We now only need  one single SSRS report to display data from any database table the user selects from the feed list in the parameter drop down -  much simpler than dozens of different reports, or dozens of datasets and playing with visibility settings etc.