0

I have a job that runs every 5 minutes. Every once in a while, if our database load is large, the job will take longer than that. The job will then no longer run, because the next scheduled time is in the past. Is there a way I can get SQL Server to work around this so that it runs again even after one of these long runs?

nfw
  • 3,853
  • 2
  • 19
  • 16
  • I would take a look at this thread: http://stackoverflow.com/questions/1200527/how-to-set-sql-server-2005-job-cmdexec-timeout I know you are not 'timing out' however you are overlapping your times, which to me sounds like the job should be broken up more or spread apart further. So instead of finding a solution to the question you asked, try tackling it from that angle. – ammills01 Jan 03 '13 at 18:01
  • @ammills01 Great, thanks. I am running a Windows app with the job, so I can't break it up further. I just used SQL's scheduler because the windows task scheduler doesn't allow for tasks to be scheduled multiple times daily. Maybe I should look into a different scheduling tool? – nfw Jan 03 '13 at 18:05
  • I have not used it but I just opened up the Task Scheduler on my Windows 7 machine and if I create a new task and go into Triggers I can set the task up 'On a schedule', Daily, and there is a section for 'Advanced settings' where I can choose to repeat the task every 5 minutes. Also, under Settings there is an option to run the task ASAP if it is missed. – ammills01 Jan 03 '13 at 20:59
  • @ammills01 Awesome, that explains it. Our server is running Windows Server 2003 and there is no option to "run the task ASAP if missed". I will have to look into another solution. I might have to add the schedule logic in my program itself and just let it run all day. – nfw Jan 03 '13 at 22:30
  • @nfw It looks like you can [schedule multiple tasks daily with Windows Server 2003](http://support.microsoft.com/kb/814596) (with the `/st` parameter, and most likely with the GUI as well). – Bernhard Barker Jan 04 '13 at 01:10

1 Answers1

0

If you were to have a script that runs continuously, you can spawn a second script from within the script every 5 minutes (without waiting for it to exit). There are many alternative (better) ways to do scheduling in Windows involving custom applications.

But then you will have overlapping script runs if one goes beyond it's 5 minutes, which is probably not what you want. A workaround is to create a temp file when the second script starts and delete it when it's done, and check for its existence in the beginning of the script, if it exists, you exit.

Community
  • 1
  • 1
Bernhard Barker
  • 54,589
  • 14
  • 104
  • 138