T-sql to check if JOB is still running

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

T-sql to check if JOB is still running

Author

Topic

DLTaylor
Posting Yak Master

136 Posts

Posted - 2010-11-15 : 09:01:07

I am using SQL 2005 sp3.

I have number of jobs on a server that run every 20 mins and complete usually within 3 to 5 mins.

On occasions one will get stuck as ‘executing’ which effectively means that particular job would not run again until I intervened.
For some reality… I have just found a Job running for 16hours – that usually lasts 3 minutes!! ;-(

Does anyone have any T-sql that will return records if a job takes longer than a given time e.g. 30 min – I can then add this to an alert.
Or any advice would be great!
Thanks

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-11-15 : 18:59:24

[CODE]use msdb

declare @DeltaMinutes int = 10

select j.name, jh.run_duration duration_in_minutes
from
msdb.dbo.sysjobs j
inner join
msdb.dbo.sysjobhistory jh
on jh.job_id = j.job_id
where
jh.run_status = 4
and ((run_duration / 10000) * 60) + ((run_duration % 10000) / 100) > @DeltaMinutes
order by
run_duration DESC[/CODE]

=======================================
In all life one should comfort the afflicted, but verily, also, one should afflict the comfortable, and especially when they are comfortably, contentedly, even happily wrong. -John Kenneth Galbraith, economist (1908-2006)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources