Need to locate jobs running past their next scheduled run time SQL2005

SQL Server 2005 SP2 (Standard Edition 32 bit)

I need some help trying to figure out the right way to query the job
information.

The issue is we every now and a then a job that normally runs in a
couple of seconds does not complete. It will still show in the job
activity page as running but the data which is being fetched from a
remote Oracle database will not be updated on the local server. If I
can get the job to kill then it will run at its next scheduled time in
a couple of seconds and we have data.

I need a query that will detect when this (or any other) job has
‘hung’ and is still running past its next scheduled run time.

Looking over the list of job tables and then looking at the
documentation it would seem that the sysjobactivty table would provide
my answer however the table has 4751 rows and many of the jobs have
not ran since 2008 so obviously there is more to understanding the
table contents

The sysjobhistory table which only has a 1000 rows in it has a
run_status column and the documentation lead me to believe it would
show a status of 3 if the job was active. But my repeated querying of
the table of a run_status of anything <> 1 always gets me no hits. I
tried running a task that takes 45 seconds to a minute but I never got
a query result.

Going back to the sysjobactivty table I tried looking for entries that
had a start time but no end time however I got a bunch of hits form
jobs submitted way back. I tried filtering the results against the
current date and I get no rows which would be true and if I change it
to look a couple months back I find two rows for jobs that never
completed and which have no sysjobhistory entries; however, I do not
know the data well enough to know if this query will produce what I
need when the problem exists.

select * from msdb.dbo.sysjobactivity
where start_execution_date is not null
and stop_execution_date is null
and run_requested_date > ( getdate() - 91) {would be within 24
hrs)

If someone out there will the knowledge of when these tables are
populated would be so kind as to provide a few pointers I would
appreciate it.

-- Mark D Powell --

On Sep 9, 5:49*pm, Erland Sommarskog wrote:
> Mark D Powell (Mark.Pow...@eds.com) writes:
>
> > Looking over the list of job tables and then looking at the
> > documentation it would seem that the sysjobactivty table would provide
> > my answer however the table has 4751 rows and many of the jobs have
> > not ran since 2008 so obviously there is more to understanding the
> > table contents
>
> I don't work that much with the msdb tables, so I don't have any
> ready recipe for you. But what happens if you join in sysjobs, and
> filter out disabled jobs?
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:http://www.microsoft.com/sql/prodinf...ons/books.mspx

I have tried several variations. I just need to confirm if the query
above would in fact show running jobs for the situaltion I mentioned.
I will go back and try to see what I have to do to filter out disabled
jobs and see if I can get any hits for actually running tasks. Not
knowing when SQL Server actually updates these tables is my real issue
since like I said sysjobhistory has a status column with a value that
indicates running but I was unable to find a long runing ( 45 sec)
task via a query when the task ran. This may be do to the history
being updated after the step/job completes where in the ancient past
the data was written directly into the history. Based on the
documentation sysjobactivity should hold the data I want.

When I figure this out I will try to post an update.

-- Mark D Powell --