A T-SQL query to get current job activity
30September 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.
SQL Studies 
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.
You are absolutely right. It can and should be removed. It was a carry over from the original query that I missed. Thanks!
Great job… I am developing a SP to control job time executions and this is the final piece i need.
Glad it helped!
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).
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.
Kenneth, Just wanted to check did you get the time to check on this.
I did, but never had any luck. I’ll take another look today.
Thanks for putting this together, Kenneth. Well done. The key for me was the SessionID.
No problems, I’m glad I could help. Now I just wish I could figure out Ankit’s problem with the current step id if you start on a step that isn’t the first.
I think another nice to have would be to show the duration of each step for a job running.
Good idea. I’ll take a look and see what I can figure out 🙂
[…] https://sqlstudies.com/2013/09/05/a-t-sql-query-to-get-current-job-activity/ […]
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?
Yep, I’m afraid so. It was the best I could manage. For 90% of the jobs I’ve seen it works fine but I’ve never been terribly happy with that next step part.
[…] A T-SQL query to get current job activity […]
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 NULLLooks good to me. Great solution 🙂
I’m getting a Null for the second step name in a test job that runs steps 1, 2 and 4. I will see what I can do to fix this after lunch 🙂
Actually, what I am really working on are two related tasks:
1. In a job with multiple steps, the last step will check whether any of the previous steps failed. If they did, it will report the errors and will then finish normally reporting an overall Success, since the errors have already been reported and the automated notification is set to report Failures only (this will still happen if that last step fails for some reason).
2. Identify when a job is running for longer than usual so that I am aware if a job gets stuck – if it’s stuck, it ain’t gonna fail and so it ain’t gonna tell me that there is a problem. SQL Server is so reliable that I don’t need to manually check for this sort of thing so, when it does happen, it can be days before I find out and that is usually because the stuck job has caused other issues.
Good Job Brenda… Your’s is better.
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?
You can check sys.dm_exec_requests but I don’t know of any way to tie it directly back to a specific job.
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.
Nice! I’ll go ahead and modify the query.
[…] Honestly, though, as much as I love writing queries for things these views are more than a bit difficult to work with. […]
In regards to the step number.
I also tried to avoid the xp_sqlagent_enum_jobs, and it works as described above. Ran into the Session ID problem also.
But am thinking of going back to the xp, why not?
The Job Activity Monitor has been using it since forever. If MSFT finally abandons it I am certain they will create substitution for all of the bits of information – via dmv-s or otherwise. Or, they might just give the xproc a documented status…
The brenda solution on January 7, 2017 at 1:05 PM is the real answer … Mr. Fisher you need to update your post. It doesn’t work, for it cannot see the correct step. While Brenda’s bring home the real answer needed.
The issue with Brenda’s version was if the “go to next step” option was selected rather than specifying the number of that step.
This code has a few cosmetic tweaks (Brenda’s isn’t wrong, I just have to format stuff my way to be able to work with it easily – solely my problem) but the real difference is the link for the second SysJobSteps table;
[sql]
SELECT 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,
CASE WHEN ja.Stop_Execution_Date IS NULL THEN ‘Running’ ELSE ” END AS RunStatus,
RIGHT(‘0’ + CONVERT(VARCHAR(2), DATEDIFF(second, ja.Start_Execution_Date, GetDate())/3600), 2)
+ ‘:’ + RIGHT(‘0’ + CONVERT(VARCHAR(2), DATEDIFF(second, ja.Start_Execution_Date, GetDate())%3600/60), 2)
+ ‘:’ + RIGHT(‘0’ + CONVERT(VARCHAR(2), DATEDIFF(second, ja.Start_Execution_Date, GetDate())%60), 2) AS Duration
FROM
msdb.dbo.SysJobActivity ja WITH (nolock)
INNER JOIN msdb.dbo.SysJobs j WITH (nolock) ON ja.Job_ID = j.Job_ID
LEFT OUTER JOIN msdb.dbo.SysJobSteps js WITH (nolock) ON (j.Job_ID = js.Job_ID
AND ISNULL(ja.Last_Executed_Step_ID, j.Start_Step_ID) = js.Step_ID)
LEFT JOIN msdb.dbo.SysJobSteps js2 WITH (nolock) ON (js.Job_ID = js2.Job_ID
AND (js.On_Success_Step_ID = js2.Step_ID
OR (js.On_Success_Action = 3 AND js.Step_ID + 1 = js2.Step_ID)))
WHERE
ja.Session_ID = (SELECT TOP 1 Session_ID
FROM msdb.dbo.SysSessions WITH (nolock)
ORDER BY Agent_Start_Date DESC)
AND ja.Start_Execution_Date IS NOT NULL
AND ja.Stop_Execution_Date IS NULL
[/sql]