stop long-running job

Question

stop long-running job

RightOnTarget

Hall of Fame

Points: 3041
More actions
July 15, 2014 at 12:08 pm

#293487

Hi all,
I am trying to write a script to stop a job if it runs outside of "normal" duration.
Here is the query I use:
SELECT
j.job_id
,j.name
,DATEDIFF(MINUTE, start_execution_date, getdate()) duration
FROM msdb.dbo.sysjobs J
JOIN msdb.dbo.sysjobactivity A ON A.job_id = J.job_id
WHERE A.run_requested_date IS NOT NULL
AND A.stop_execution_date IS NULL
If duration is outside of norm, then I run: EXEC msdb.dbo.sp_stop_job @job_id = "failed_job_id"
There are some old jobs that do not have stop_execution_date populated for some reason, so my query always selects them.
Is there a way to insert a date or somehow remove these old jobs?
Thanks,

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Those jobs shouldn't have anything in sysjobactivity if they aren't running. Why not just have your query return jobs outside your threshold by doing:

SELECT

j.job_id,

j.name

FROM

msdb.dbo.sysjobs J

JOIN msdb.dbo.sysjobactivity A

ON A.job_id = J.job_id

WHERE

A.run_requested_date IS NOT NULL AND

A.stop_execution_date IS NULL AND

A.start_execution_date < DATEADD(MINUTE, -10, GETDATE())

Replace the 10 with whatever your "normal" is.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

RightOnTarget Hall of Fame Points: 3041 More actions · Answer 2

Thanks for reply, but each job has different "normal" execution time, so a proposed query won't work.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

How are you determining normal execution time for a job?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

RightOnTarget Hall of Fame Points: 3041 More actions · Answer 4

At this point, I simply look at the job history to determine the max time it ever ran for.

Not ideal, but it works.