Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

A T-SQL query to get current job activity

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

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...