A T-SQL query to get current job activity

25

September 5, 2013 by Kenneth Fisher

I recently had the task of collecting job activity for the purposes of turning it into a report for some business users. After some searching on the web and BOL I found the stored procedure msdb.dbo.sp_help_jobactivity that returns the data I need (with some work) and a lot more data I don’t need.

I could create a table and run the data into it. Then I would be able to processes the data however I want. However I want to know how exactly it’s getting the data. So I decided to use sp_helptext to get the code behind this stored procedure.

USE msdb
GO
EXEC sp_helptext [dbo.sp_help_jobactivity]
GO

In the code I find the following query:

SELECT
    ja.session_id,                
    ja.job_id,
    j.name AS job_name,
    ja.run_requested_date,        
    ja.run_requested_source,      
    ja.queued_date,               
    ja.start_execution_date,      
    ja.last_executed_step_id,     
    ja.last_executed_step_date,   
    ja.stop_execution_date,       
    ja.next_scheduled_run_date,
    ja.job_history_id,            
    jh.message,
    jh.run_status,
    jh.operator_id_emailed,
    jh.operator_id_netsent,
    jh.operator_id_paged
  FROM
    (msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id)
    join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id
  WHERE
    (@job_id IS NULL OR ja.job_id = @job_id) AND
     ja.session_id = @session_id

So first the variables. @job_id is generated from a job name passed into the SP. I don’t need it so I’m going to remove that line from the WHERE clause. The @session_id is a bit more important. Every time the agent is started a new session_id is added to the syssessions table. @session_id is the value of the current session for the agent.

Next we need to look at the tables/views that we are pulling the data from. Sysjobactivity, sysjobhistory and syssessions are documented in BOL although it should be noticed that syssession is only accessible by a sysadmin. Sysjobs_view is undocumented, and if you use sp_helptext to break it down you eventually get to sysoriginatingservers which is also undocumented.

So why should I care? Well if I am developing anything that I plan on being in use for any extended period of time then I have to be very wary of using anything undocumented as it may disappear or change without any notice. However in my case this is a short term project.

Just out of curiosity does anyone really believe it when they are told “It doesn’t have to be good code since it’s only going be in place for a short period of time”?

Now, the information I am looking for includes what jobs are running, what step is currently running for each job, and how long it’s been running for. Basically similar information to the Job Activity Monitor. So to get the currently running jobs I’m going to add a WHERE clause to check for an existing start_execution_date and a missing stop_execution_date. I’m also going to only pull those columns I care about. And last but not least I’m going to use a subquery instead of a variable to get the most recent session_id.

Now as it happens the main differences between sysjobs_view and sysjobs, at least for our purposes, are the originating_server and the master_server. And fortunate for us we don’t need those columns right now, so we can switch to sysjobs and avoid the problem of using an undocumented system view.

SELECT
	ja.job_id,
	j.name AS job_name,
	ja.start_execution_date,      
	ja.last_executed_step_id
FROM msdb.dbo.sysjobactivity ja 
LEFT JOIN msdb.dbo.sysjobhistory jh 
	ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j 
	ON ja.job_id = j.job_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
AND start_execution_date is not null
AND stop_execution_date is null;

At this point if you run the query you will notice that the last_executed_step_id is the id of the last step completed. If the job is on the first step it’s NULL. So if I want the current step it’s a simple formula of ISNULL(last_executed_step_id,0)+1. Then a join to sysjobsteps to get the step name.

SELECT
	ja.job_id,
	j.name AS job_name,
	ja.start_execution_date,      
	ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
	Js.step_name
FROM msdb.dbo.sysjobactivity ja 
LEFT JOIN msdb.dbo.sysjobhistory jh 
	ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j 
	ON ja.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps js
	ON ja.job_id = js.job_id
	AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY session_id DESC)
AND start_execution_date is not null
AND stop_execution_date is null;

And there you go, a query that pulls the currently running jobs and what step they are on. Use the query in a report and you can give your users access to what jobs are currently running without using the Job Activity Monitor.

Note: I’ve modified the subquery that pulls the latest session based on a comment below. I was ordering by the agent_start_date but Mads Soegaard (thanks btw!) noticed an odd situation where a session 15 was listed as starting after a session 1015 causing a problem. I’ve changed to use session_id. It’s an identity column so should be (fair warning, there are cases where your identity values will be out of order, or even skip values) created in order meaning the largest number would be the latest value. If you run into problems with this you can always switch back to using agent_start_date.

25 thoughts on “A T-SQL query to get current job activity

  1. JJM says:

    I believe you can actually eliminate the LEFT JOIN to msdb.dbo.sysjobhistory, since the LEFT JOIN is not doing any row filtering, and since you are not using any of the columns from this table in the query.

  2. webponte says:

    Great job… I am developing a SP to control job time executions and this is the final piece i need.

  3. Ankit Agrawal says:

    Thanks a lot for the above query….

    I have one doubt, Suppose a job has 4 steps and I start a job from 3rd step then what will be the value of last_executed _step_id, whether it will be NULL or it will be 2.

    • I don’t have time today to actually run a test today, but based on the definition in BOL of sysjobactivity.last_executed_step_id it should return you the actual step number. So in your example it would return 3 (3rd step of the job).

      • Ankit Agrawal says:

        Thanks for the prompt reply…

        As you mentioned in this article “If the job is on the first step last_executed_step_id is NULL”.
        That’s why we are using ISNULL(last_executed_step_id,0)+1 as current_executed_step_id. As per my understanding from the above article last_executed_step_id will be the last completed step of a job in the current run and if no step is completed then it will be NULL. Since in this example we are triggering the job from 3rd step and if the job is running in the 3rd step only, ideally last_executed_step_id value should be NULL.

        Please correct me if I am wrong.

        Sorry, I don’t have permission to test on it.

        • No, you are absolutely correct. Until a step has completed the value is NULL so you will get an assumed 1. Unfortunately I don’t know how to avoid the problem. I will do some looking around and see what I can come up with.

  4. Ankit Agrawal says:

    Kenneth, Just wanted to check did you get the time to check on this.

  5. Jeff Moden says:

    Thanks for putting this together, Kenneth. Well done. The key for me was the SessionID.

  6. Craig says:

    I think another nice to have would be to show the duration of each step for a job running.

  7. peoria says:

    Nice query. However, here is the issue I see, correct me if I’m wrong. If I set a job step to jump past another job step, then it won’t equal the job step + 1, will it?

  8. […] A T-SQL query to get current job activity […]

  9. brenda says:

    Realize that this is an older thread, but I really needed to get the current step name. Believe that this will get it. Joined sysjobsteps with sysjobsteps to get the next step name. Then in the SELECT use a CASE to get the Step Name.

    SELECT distinct j.name as JobName, CASE WHEN ja.last_executed_step_id IS NULL THEN js.step_name  ELSE js2.step_name END as StepName,
         ja.start_execution_date as StartDateTime, 'Running' AS RunStatus, 
     (SELECT RIGHT('0' + CONVERT(VARCHAR(2), DATEDIFF(second, start_execution_date, GetDate())/3600), 2) 
    	 + ':' + RIGHT('0' + CONVERT(VARCHAR(2), DATEDIFF(second, start_execution_date, GetDate())%3600/60), 2) 
    	 + ':' + RIGHT('0' + CONVERT(VARCHAR(2), DATEDIFF(second, start_execution_date, GetDate())%60), 2) ) as Duration,
    	 '' as message
     FROM msdb.dbo.sysjobactivity ja 
        JOIN msdb.dbo.sysjobs j
    	    ON ja.job_id = j.job_id
        LEFT JOIN msdb.dbo.sysjobsteps js  
    	    ON j.job_id = js.job_id
        	    AND CASE WHEN ja.last_executed_step_id IS NULL THEN j.start_step_id 
    			 ELSE ja.last_executed_step_id
    			 END = js.step_id		    
        LEFT JOIN msdb.dbo.sysjobsteps js2
    	    ON js.job_id = js2.job_id
    	    AND js.on_success_step_id = js2.step_id
     WHERE ja.session_id = ( SELECT TOP 1 session_id
    					FROM msdb.dbo.syssessions
    					ORDER BY agent_start_date DESC )
     AND start_execution_date IS NOT NULL
     AND stop_execution_date IS NULL
  10. Michael McCormick says:

    I’ve found that if I run this query immediately after executing sp_start_job, it will not yet show that job as running, but a second later (and for the duration of the job) it will. Putting a WAITFOR DELAY before checking would likely work, but seems clumsy. Any thoughts on how to check that the job has started before you check that the job is running or finished?

  11. Mads Soegaard says:

    Hi, we recently had a problem using this query, which happened after a series of restarts of the sql server.

    It turned out that the session id’s created by sql server, were 14,15,1015, but with ID 15 having a startdate after 1015. According to the query above then ID 15 should be the latest session, but it was in fact 1015 that was the latest. Therefore we detected no running jobs.

    So our solution was to order by session_id, instead of agent_start_date in the subselect.

  12. […]   Honestly, though, as much as I love writing queries for things these views are more than a bit difficult to work with. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,664 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: