Every once in a while (say 3-4 weeks apart), one or more of my
scheduled packages will "hang" and never complete.

I don't get an error, and the job will just run until I check the
SqlAgent myself, see it has been "running" for 87 hours, stop the job
manually and restart it. Of course, nothing is actually happening
during this time, and because it is marked as running, the scheduler
will not kick off another job.

The packages this happens to are those that pull remote information
from a linked Oracle Server to my Sql Server 2000 (sp3).

Now, I figure that I should be able to set some kind of timeout on the
scheduled package. This way, if it goes beyond a certain time, it
will just automatically cancel itself and go back into the queue to be
run again at the next scheduled point.

I know this can be done with the general Windows scheduler.

Now, I looked through the options in the scheduler tabs, and I read
through BOL, but I didn't find anything.

Am I missing something simple? Can this be done as I've laid out?

Thanks,
JK

*Bump*

Any ideas?
JK

Jeffrey --

I have no help for you, but I have exactly the same problem. I'm hoping that someone can step up and help -- it would be incredibly handy to be able to set a timeout on a SQL Server Agent Job.

Chris

"Chris" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Wow, this is quite a shame.

Has anyone run into this issue and solved it?

I'd rather not have to wait for Yukon....

JK

In article <2264ae0.0312141315.7e80d3d8 (AT) posting (DOT) google.com>, Jeffrey
<jeffkretz (AT) hotmail (DOT) com> writes
Two ways to approach this-

1 Find out what us causing the process to hang. Now this probably
depends on what the package is doing, but a good start place is anything
that could raise an error dialog that waits for you to click OK.
Examples could be a badly written COM object, or a bad OLE-DB/ODBC
driver that decides to ask for credentials or just throws an error. Old
debug type MsgBox statements left in code is another favourite.
Obviously start by turning on all logging, task logs, package to file,
package to SQL Server, and the job step output to file option.

2 Detect and warn or resolve jobs running over a long period of time or
a suitably calculated threshold. I have implemented such a mechanism as
standard across all my servers, as whilst I don't have (an ongoing)
problem with Dts, there are other processes that can exhibit this
behaviour.

The key to my approach is that every job has a start step that logs the
start time to a table. It also calculates a duration threshold which the
job should not exceed. The threshold is based on a simple calculation,
including the past history as logged in sysjobhistory.

I then have a regularly running job that uses xp_sqlagent_enum_jobs to
detect which jobs are still running. Jobs that have completed are also
cleaned up at this point. Jobs that have exceeded the duration threshold
can then be "actioned", although I just report on them.



I would go with option 1, and use option 2 as a fallback.
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Darren Green <darren.green (AT) reply-to-newsgroup-only (DOT) uk.com> wrote

Thank you very much for this, those options make sense.

As near as I can tell, the hang-up seems to relate to my connection
(via a distributed query) to an external Oracle Database at another
location. In every case, stopping and restarting the job has resolved
the issue.

So, as far as you know, there is no internal method of setting a time
limit on a scheduled job? It would be necessary to write a process
that trolls the job activity and reports/handles hung jobs?

Thanks again,
JK

In article <2264ae0.0312161417.1fa52391 (AT) posting (DOT) google.com>, Jeffrey
<jeffkretz (AT) hotmail (DOT) com> writes
Nope. An alternative scheduler may offer this functionality, but this
loses any integration with SQL Server, and could be more hassle than it
is worth.


I can send you a script for my system. You can then just add some logic
to determine what to do when it detects a long running job. Is the email
shown as your posting address valid?

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Darren Green <darren.green (AT) reply-to-newsgroup-only (DOT) uk.com> wrote

Darren,

That would be terrific, go ahead and send it to the hotmail address
above. Have to say that I've found your site invaluable since I first
ran into it last year. It's a Class A service, thanks a lot for your
efforts in this.

JK

Darren

May I request that script as well. I recently moved my databases and DTS to a new box and every few days my job just hangs, but looking at the log it appears that it doesn't even begin to process

Thank
Ze

----- Darren Green wrote: ----

In article <2264ae0.0312161417.1fa52391 (AT) posting (DOT) google.com>, Jeffrey
<jeffkretz (AT) hotmail (DOT) com> write
Nope. An alternative scheduler may offer this functionality, but this
loses any integration with SQL Server, and could be more hassle than it
is worth

I'm afraid so

I can send you a script for my system. You can then just add some logic
to determine what to do when it detects a long running job. Is the email
shown as your posting address valid

--
Darren Green (SQL Server MVP
DTS - http://www.sqldts.co

PASS - the definitive, global community for SQL Server professional
http://www.sqlpass.or

In message <872F4060-D195-4104-BF82-35C7872496F9 (AT) microsoft (DOT) com>, Zev
<zev.chopp (AT) verizon (DOT) net> writes
Sent.



--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org