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]


MS Sql Server LazyDBA home page