Kill job that is executing endlessly

Kill job that is executing endlessly

Post by Mark » Tue, 24 May 2005 22:52:46


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
 
 
 

Kill job that is executing endlessly

Post by JT » Wed, 25 May 2005 04:22:02

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.



executes
in
Timeout
been
that

 
 
 

Kill job that is executing endlessly

Post by Helge C. R » Wed, 25 May 2005 04:40:58

Hi,



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
 
 
 

Kill job that is executing endlessly

Post by JT » Wed, 25 May 2005 05:36:56

Sure, if you must be a minimalist. ;-)




the jobs.
SQLDMO_JOBEXECUTION_STATUS)
 
 
 

1. I am ready to kill kill kill!!!

2. Avoid executing a job with quartz scheduler - HELP please i am stumped

3. "germany jobs" "germany job sites" "germany job search" "jobs in germany" "german jobs" "germany jobs it" "germany jobs for foreigners" "germany jobsite" "germany jobs in english" on http://jobs-germany.blogspot.com/

4. "germany jobs" "germany job sites" "germany job search" "jobs in germany" "german jobs" "germany jobs it" "germany jobs for foreigners" "germany jobsite" "germany jobs in english" on http://jobs-germany.blogspot.com/

5. "germany jobs" "germany job sites" "germany job search" "jobs in germany" "german jobs" "germany jobs it" "germany jobs for foreigners" "germany jobsite" "germany jobs in english" on http://jobs-germany.blogspot.com/

6. "germany jobs" "germany job sites" "germany job search" "jobs in germany" "german jobs" "germany jobs it" "germany jobs for foreigners" "germany jobsite" "germany jobs in english" on http://jobs-germany.blogspot.com/

7. "germany jobs" "germany job sites" "germany job search" "jobs in germany" "german jobs" "germany jobs it" "germany jobs for foreigners" "germany jobsite" "germany jobs in english" on http://jobs-germany.blogspot.com/

8. "germany jobs" "germany job sites" "germany job search" "jobs in germany" "german jobs" "germany jobs it" "germany jobs for foreigners" "germany jobsite" "germany jobs in english" on http://jobs-germany.blogspot.com/

9. "germany jobs" "germany job sites" "germany job search" "jobs in germany" "german jobs" "germany jobs it" "germany jobs for foreigners" "germany jobsite" "germany jobs in english" on http://jobs-germany.blogspot.com/

10. AM/AM, AM/AM Modeling

11. "finland jobs" "google finland jobs" "helsinki finland jobs" "finland jobs english" "jobs in finland for foreigners" "jobs in finland for english" "jobs finland" http://jobs-in-finland.blogspot.com

12. "JOBS IN FINLAND" "JOBS FINLAND" "FINLAND JOBS" "ACCOUNTS JOBS IN FINLAND" "FINANCE JOBS IN FINLAND" "AGRI JPBS IN FINLAND" "MEDICAL JOBS IN FINLAND" ON JOBS http://jobs-in-finland.blogspot.com/