November 5, 2009 at 10:58 am
I asked this question in another forum and unfortunately I still do not have an answer. This is the problem:
I have several scheduled tasks that run every 10-30 seconds. All this tasks run very well, they take in the worst cases 2 seconds to run, but usually is less than a second (History shows 00:00:00), when I run them in SQL they are almost instantaneous too, so everything is fine... Except there is one of this tasks, which connects to an Oracle Linked Server (there are two more that connect to the same oracle database, other tasks connects to Sybase and yet other tasks connects to old foxpro tables, but those are not the issue) that once or twice a day decides to keep the query running ad-infinitum. Only way to stop it is to restart the SQL Server Agent, and then it runs smoothly for some time.
My first thought to fix this problem is to limit the running time of the Jobs, lets say if they take more than 5 seconds then just cancel it and try again the next scheduled time, unfortunately I am not able to find information of how to do it (my google skills are not up to par this days it seems!), is it possible to do it? And how?
I know it would be better to find the causes of this instead of treating the symptoms, but I thought it would be useful to have something like this anyways for all the scheduled jobs, so one can analyze the logs and when there are jobs that are "timing out" then one can try to find the problem without the need to stopping the job while investigating.
I looked at several time out settings, like Connection Timeout and Query Timeout for the linked servers, they do not end this query (it is actually a simple dynamic SQL that reads data from the remote table(s) and insert record(s) in local table(s)) and the problem is always with the same job, the others run without problem even the others that are using the same Oracle Linked Server.
Now I am thinking of having yet another job that checks the job history and re-starts the Agent service if it notices a big gap on them. (By the way, restarting the agent is the only way to stop that query)
Any help would be much appreciated, thanks
Hugo
August 27, 2012 at 9:17 am
I hate to bump an old thread like this, but I have almost exactly the same need and didn't want to post a brand new topic for this. The only solution I can come up with is to add a separate job that checks the running time of the job in question (some how), and if it has exceeded my expected run time to cancel the job and/or kill the SPID, but is there a more elegant way to accomplish this?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply