Thanks very much.
DD
-----Original Message-----
From: Mark Harr
[mailto:mssqldba-ezmlmshield-x52053616.[Email address protected]
Sent: Thursday, February 24, 2005 9:10 AM
To: LazyDBA Discussion
Subject: RE: Long Running Jobs Query
The previous suggested query would return information on "completed" job steps, not job steps that are currently running.
I don't have the code where I had queried in progress jobs previously, but I can offer a few hints to help you develop it on your own. The table you want to check is the sysjobhistory, and check the run_status = =3D
4 (for inprogress step) and run_date for the start time of the step.
Then, by joining sysjobsteps and sysjobs, you can find the name, etc for the job to make the query meaningful.
However, (from my memory of doing this previously) there is a trick to it. Records are not written to sysjobhistory until at least one step has completed. So I added a dummy step to each job to have a quick first step to provide that "kick-off" action. I think I just did something like a T-SQL "PRINT GETDATE()" or something similar, hoping to add the start date/time to the job history log.
Sorry I couldn't provide more complete solution. Hope this will help point you in the right direction.
Mark
-----Original Message-----
From: Davis Diane (DOC-CEN)
[mailto:mssqldba-ezmlmshield-x71327466.[Email address protected] <mailto:mssqldba-ezmlmshield-x71327466.[Email address protected]
Sent: Thursday, February 24, 2005 8:39 AM
To: LazyDBA Discussion
Subject: RE: Long Running Jobs Query
And I am still trying to narrow this down to currently running jobs, which these queries don't yet do.
DD
-----Original Message-----
From: Albert van Biljon
[mailto:mssqldba-ezmlmshield-x51405770.[Email <mailto:mssqldba-ezmlmshield-x51405770.[Email> address protected]
Sent: Thursday, February 24, 2005 4:16 AM
To: LazyDBA Discussion
Subject: RE: Long Running Jobs Query
Hi,
Just a question about your query: Why did you write it in that way?
Won't this one work as well?
SELECT sj.Name, SUM(last_run_duration) nDuration
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobsteps sjs ON sjs.Job_Id =3D
sj.Job_Id
GROUP BY sj.Name, sjs.JOB_Id
Regards,
Albert
--- Schlieper Paul
<mssqldba-ezmlmshield-x30491964.[Email address protected]
wrote:
> You're welcome. Note, the nDuration is seconds in INT, so 200 =3D 3 m
> =
20
> s.
>=20
> P
>=20
> -----Original Message-----
> From: Davis Diane (DOC-CEN)
> [mailto:mssqldba-ezmlmshield-x17965794.[Email <mailto:mssqldba-ezmlmshield-x17965794.[Email>
> address protected]
> Sent: Tuesday, February 22, 2005 2:41 PM
> To: LazyDBA Discussion
> Subject: RE: Long Running Jobs Query
>=20
>=20
> Thank you very much!
> DD
>=20
>=20
> -----Original Message-----
> From: Schlieper Paul [mailto:Paul.[Email <mailto:Paul.[Email> address protected]
> Sent: Tuesday, February 22, 2005 2:38 PM
> To: [Email address protected]
> Subject: RE: Long Running Jobs Query
>=20
>=20
> **A LazyDBA.com subscriber has responded to your lazydba.com post**=20
>**LazyDBA.com mail shield has forwarded you this email, **and
>removed=20 any attachments, and kept your email address secret **from
>this=20 person, and any viruses/trojans.
> **If you reply to this email, the person will see your email
>address=20 as normal **Anything below this line is the original email
>text =20 =20 =20 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 =3D sj.Job_Id
> WHERE nDuration >=3D <some threshold>
> ORDER BY Name
>=20
> P
>=20
>=20
> -----Original Message-----
> From: Davis Diane (DOC-CEN)
> [mailto:mssqldba-ezmlmshield-x26586713.[Email <mailto:mssqldba-ezmlmshield-x26586713.[Email>
> address protected]
> Sent: Tuesday, February 22, 2005 1:54 PM
> To: LazyDBA Discussion
> Subject: Long Running Jobs Query
>=20
>=20
> Hi,
>=20
> I am trying to write a query on the jobs system tables to show me
>all=20 jobs that are currently running for too long a time, over a
>certain=20 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.
>=20
> Thanks for any help,
>=20
> Diane Davis
>=20
>=20
>=20
>
---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
>Website=20
> : http://www.LazyDBA.com <http://www.LazyDBA.com/> To unsubscribe:
> http://www.lazydba.com/unsubscribe.html <http://www.lazydba.com/unsubscribe.html>
> For additional commands, e-mail: mssqldba-[Email address protected]
>=20 =20 =20 =20 =20
>
---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
>Website=20
> : http://www.LazyDBA.com <http://www.LazyDBA.com/> To unsubscribe:
> http://www.lazydba.com/unsubscribe.html <http://www.lazydba.com/unsubscribe.html>
> For additional commands, e-mail: mssqldba-[Email address protected]
>=20 =20 =20
>
---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
>Website=20
> : http://www.LazyDBA.com <http://www.LazyDBA.com/> To unsubscribe:
> http://www.lazydba.com/unsubscribe.html <http://www.lazydba.com/unsubscribe.html>
> For additional commands, e-mail:
> mssqldba-[Email address protected]
>=20
>=20
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website : http://www.LazyDBA.com <http://www.LazyDBA.com/> To unsubscribe: http://www.lazydba.com/unsubscribe.html <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 <http://www.LazyDBA.com/> To unsubscribe: http://www.lazydba.com/unsubscribe.html <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