You're welcome. Note, the nDuration is seconds in INT, so 200 = 3 m 20 s.

P

-----Original Message-----
From: Davis Diane (DOC-CEN)
[mailto:mssqldba-ezmlmshield-x17965794.[Email address protected]
Sent: Tuesday, February 22, 2005 2:41 PM
To: LazyDBA Discussion
Subject: RE: Long Running Jobs Query


Thank you very much!
DD


-----Original Message-----
From: Schlieper Paul [mailto:Paul.[Email address protected]
Sent: Tuesday, February 22, 2005 2:38 PM
To: [Email address protected]
Subject: RE: Long Running Jobs Query


**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email,
**and removed any attachments, and kept your email address secret
**from this person, and any viruses/trojans.
**If you reply to this email, the person will see your email address as normal
**Anything below this line is the original email text



SELECT sj.Name, tmp1.nDuration
FROM msdb.dbo.sysjobs sj
JOIN (SELECT Job_Id, SUM(last_run_duration) AS nDuration
FROM msdb.dbo.sysjobsteps
GROUP BY JOB_Id) AS tmp1 ON tmp1.Job_Id = sj.Job_Id
WHERE nDuration >= <some threshold>
ORDER BY Name

P


-----Original Message-----
From: Davis Diane (DOC-CEN)
[mailto:mssqldba-ezmlmshield-x26586713.[Email address protected]
Sent: Tuesday, February 22, 2005 1:54 PM
To: LazyDBA Discussion
Subject: Long Running Jobs Query


Hi,

I am trying to write a query on the jobs system tables to show me all jobs that are currently running for too long a time, over a certain duration limit. I have looked at the system tables and the sp_help_job stored procedure but I can't quite see how to do it.

Thanks for any help,

Diane Davis



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]





---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]


MS Sql Server LazyDBA home page