SQL Agent Job: Determine how long it has been running

Question

The Scenario

There are certain SQL Agent Jobs that are scheduled to run every few minutes throughout the day.

There are legitimate times when it will miss its next schedule because it's still running from the previous schedule.

Every once and a while, a job might 'hang'. This doesn't produce a failure (since the job hasn't stopped yet). When this happens, the job can be manually stopped and works fine the next time it runs. It's designed to pick back up where it left off.

What's the most efficient way...?

I'd like a way to determine how long (in seconds) a SQL Agent Job named 'JobX' is currently running. If it isn't currently running, we can just return zero.

This way, I can stop the job if it has been running for an amount of time beyond a certain threshold.

I assume that a combination of xp_sqlagent_enum_jobs and sysjobhistory could be used, but I'm curious if there are better solutions out there... and can hopefully benefit from the obstacles the rest of you have already run into and worked around.

Solution 1

This solution would work:

SELECT DATEDIFF(SECOND,aj.start_execution_date,GetDate()) AS Seconds
FROM msdb..sysjobactivity aj
JOIN msdb..sysjobs sj on sj.job_id = aj.job_id
WHERE aj.stop_execution_date IS NULL -- job hasn't stopped running
AND aj.start_execution_date IS NOT NULL -- job is currently running
AND sj.name = 'JobX'
and not exists( -- make sure this is the most recent run
    select 1
    from msdb..sysjobactivity new
    where new.job_id = aj.job_id
    and new.start_execution_date > aj.start_execution_date
)

This a more general check dependent on system tables. If you'd prefer a custom route, you could have the job insert into a job log table you created instead.

Solution 2

/**** FOR CURRENTLY RUNNING JOBS ****/
SELECT j.name AS Job_Name,DATEDIFF(ss,a.start_execution_date ,GETDATE ())   
FROM msdb.dbo.sysjobactivity a INNER JOIN msdb.dbo.sysjobs j 
ON a.job_id =j.job_id
WHERE CONVERT(DATE,a.start_execution_date )=CONVERT(DATE,GETDATE ())
AND a.stop_execution_date IS NULL


/*This code will give u the Name of currently running jobs and for how much time it is running & after that u can add filters to it as u wish*/
/*Thanks in advance*/

Comments

[Kevin Fairchild]

Wouldn't you want something like this added on, to further limit it just to the most recent activity? "inner join (SELECT MAX(start_execution_date) AS StartDate, job_id FROM msdb..sysjobactivity GROUP BY job_id) MostRecentActivity on sj.job_id = MostRecentActivity.job_id AND aj.start_execution_date=MostRecentActivity.StartDate"

[Zhenny]

Not necessarily. If you are only looking for a job that is currently running and running longer than you expect, this query should only return to you what is currently running. If it is not running, it returns nothing.

[Kevin Fairchild]

In practice, the results given on a job that is currently running actually gives a bunch of records back. Limiting it with that JOIN seems to give the correct values. I modified your answer and accepted.

[Zhenny]

Is that due to the issue you mentioned before where the job hangs and you have to manually stop it? I'm curious as to what the extra returned entries are. If there are extra returned entries, the query also runs into the issue of returning the difference between when the last job hung even if the job is not running. I'm going to modify the query a little as the join can be replaced with a top 1.

[Kevin Fairchild]

Zhenny, using the new code, I get results back like "11" or "12" while it is running and then results like "22916011" for the rest of the time. As soon as the job starts again, it goes back to single and double-digits. When the JOIN was there, I was not getting these results. I'd either see the number of seconds while it was running or no result.

[Kevin Fairchild]

I checked the sysjobactivity table it looks like there are old records that meet your criteria. So while the job isn't running, it's finding old activity. In this case, there's an entry with a start_execution_date of "2011-09-01 10:47:20.000" and no stop_execution_date. So when I was JOINing based on the MAX start date, it would pull back the match for either the currently-executing job or the most recently-executed job (which wouldn't meet the criteria in the WHERE, which prevents incorrect data being displayed)

[Zhenny]

I'm sorry. I missed the fact that any new runs could also be the MAX(start_execution_date). I edited the query again.

[Kevin Fairchild]

Perfect. Works like a champ now.