|
Home > Archive > MS SQL Server DTS > May 2005 > Kill job that is executing endlessly
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Kill job that is executing endlessly
|
|
|
| I have a DTS package that is scheduled to pull read-only data from an
Informix server every 10 minutes. When it works, it takes less than 10
seconds. I have *zero* control over the Informix server, so all error
handling *must* be done on the SQL Server side. At times, this job executes
endlessly as if the table being read is locked, or similar. In the DTS, in
the connection to the Informix server, I have specified the Connect Timeout
and General Timeout both as 30 (seconds) in the Advanced Connection
Properties. However, this morning I check the job manually and it has been
executing since Saturday morning.
Are there work arounds for this on the SQL Server side? It strikes me that
some connection property should allow for an automatic stop of the
job/query, but I'm willing to create a programatic solution if that is the
only way.
SQL Server 2000, Windows 2003 Server, all patches.
Thanks in advance.
Mark
| |
|
| I'm almost certain this would work, but it's ugly. In job #1 (the one which
executes the DTS package in question), insert a record into a log table with
getdate() in a column called [time_started]. On completion of the package,
update the [time_completed] column of the same record with getdate(). You
now have a record of when job #1 started and when job #1 completes. If
[time_completed] is NULL, then the job is still running. Have another
scheduled job (job #2) which every 1 hour or so querys this log table to
determine if job #1 has been running for more than say 1/2 hour. If so, then
execute the sp_stop_job procdure to stop job #1.
"Mark" <Mark@nowhere.com> wrote in message
news:ecEm065XFHA.3184@TK2MSFTNGP15.phx.gbl...
> I have a DTS package that is scheduled to pull read-only data from an
> Informix server every 10 minutes. When it works, it takes less than 10
> seconds. I have *zero* control over the Informix server, so all error
> handling *must* be done on the SQL Server side. At times, this job
executes
> endlessly as if the table being read is locked, or similar. In the DTS,
in
> the connection to the Informix server, I have specified the Connect
Timeout
> and General Timeout both as 30 (seconds) in the Advanced Connection
> Properties. However, this morning I check the job manually and it has
been
> executing since Saturday morning.
>
> Are there work arounds for this on the SQL Server side? It strikes me
that
> some connection property should allow for an automatic stop of the
> job/query, but I'm willing to create a programatic solution if that is the
> only way.
>
> SQL Server 2000, Windows 2003 Server, all patches.
>
> Thanks in advance.
>
> Mark
>
>
| |
| Helge C. Rutz 2005-05-23, 8:24 pm |
| Hi,
JT wrote:
> I'm almost certain this would work, but it's ugly. In job #1 (the one
> which executes the DTS package in question), insert a record into a
> log table with getdate() in a column called [time_started]. On
> completion of the package, update the [time_completed] column of the
> same record with getdate(). You now have a record of when job #1
> started and when job #1 completes. If [time_completed] is NULL, then
> the job is still running. Have another scheduled job (job #2) which
> every 1 hour or so querys this log table to determine if job #1 has
> been running for more than say 1/2 hour. If so, then execute the
> sp_stop_job procdure to stop job #1.
I don't think you need this extra table.
You can just call sp_help_job to get start time and execution status of the jobs.
When you check wether status is 1 or maybe some other status (see SQLDMO_JOBEXECUTION_
STATUS)
and compare the date & time you should be able to fined timedout jobs.
Helge
| |
|
| Sure, if you must be a minimalist. ;-)
"Helge C. Rutz" < news050424@nurfuersp
am.de> wrote in message
news:3fepuaF7ecu8U1@
individual.net...
> Hi,
>
> JT wrote:
>
> I don't think you need this extra table.
> You can just call sp_help_job to get start time and execution status of
the jobs.
> When you check wether status is 1 or maybe some other status (see
SQLDMO_JOBEXECUTION_
STATUS)
> and compare the date & time you should be able to fined timedout jobs.
>
> Helge
|
|
|
|
|