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.