SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Timing out scheduled jobs


Timing out scheduled jobs

Author
Message
hugo r
hugo r
Old Hand
Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)

Group: General Forum Members
Points: 309 Visits: 98
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
Bad_Karma
Bad_Karma
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 Visits: 190
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search