Timing out scheduled jobs

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » SQL Server Agent » Timing out scheduled jobs

Timing out scheduled jobs

Rate Topic

Display Mode

Topic Options

Author

Message

hugo r

Posted Thursday, November 05, 2009 10:58 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, February 21, 2013 11:59 AM
Points: 307, Visits: 79

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

Post #814441

Bad_Karma

Posted Monday, August 27, 2012 9:17 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 7:32 AM
Points: 39, Visits: 136

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?

Post #1350445

« Prev Topic | Next Topic »

Permissions