How to check if a sql job is running for a long time

how to check if one of the sql job is running for long time.
i have seen some of my jobs when they fails they just keep running for the long time.
I would like to find our if the job is running for a long time (6 hours) then just stop the job and run it again.
Junior_DeveloperAsked:
Who is Participating?
 
twoboatsConnect With a Mentor Commented:
sry - missed a bit

select      datediff(m,getdate(), min(h.run_date)) as minutes_running
from      msdb..sysjobs j, msdb..sysjobhistory h
where      j.name = 'your job name'
and      j.job_id = h.job_id
and      exists (select 1 from msdb..sysjobhistory h2
                       where h2.job_id = h.job_id and h2.run_status = 4)
0
 
twoboatsCommented:
Do you mean sql agent job, or a sql query.

If it's a sql agent job, the sysjobhistory inj msdb gives you duration

If it's a query, you can use SQL profiler to monitor sql commands
0
 
Junior_DeveloperAuthor Commented:
i meant sql agent job. i want to write a query to check if the job is running longer than lets say 6 hours.
can you help me how would i use sysjobhistory to find that out?
0
 
twoboatsCommented:
For a running job, duration doesn't help, as it not set until completion.

Something like this

select      datediff(m,getdate(), min(h.run_date)) as minutes_running
from      msdb..sysjobs j, msdb..sysjobhistory h
where      j.name = 'your jbo name'
and      j.job_id = h.job_id
and      exists (select 1 from msdb..sysjobhistory h2 where h2.run_status = 4)
0
All Courses

From novice to tech pro — start learning today.