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 agent_start_date 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.

Filed under: Microsoft SQL Server, SQL Agent Jobs, SQLServerPedia Syndication, System Functions and Stored Procedures, T-SQL Tagged: code language, language sql, microsoft sql server, problem resolution, SQL Agent Jobs, sql statements, T-SQL