January 30, 2007 at 9:23 am
We have scheduled a job to run every minute, and that job invokes a few stored procedures. One of the stored procedures seems to run anywhere from five seconds to five minutes (I guess depending on DB load?). It seems if the stored procedure runs more than 45 seconds or so, the job will not execute in the next minute. Instead, it skips a minute, but does execute in the minute following the skipped minute. Has anyone had any similar problems or know of a work around?
January 30, 2007 at 9:28 am
Hi Rowdy,
My understanding is that this is exactly what you should see. If a job is already running, then I don't believe it will spawn a 2nd, 3rd, etc. job. Hence, why you only kick off again once any previous running has completed.
Rgds iwg
January 30, 2007 at 12:17 pm
- a job can only run once at a time !
- if a job runs, you cannot start it again until in has ended.
- So what you see is normal !
- in your case, suppose you could start it twice (or more), so your stored procs run x-times simultaniously and may lock oneanother causing deadlocks !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 31, 2007 at 3:36 am
options...
1. re-design the SP to guarantee to run within 1 minute.
2. re-structure your hardware to guarantee to run within 1 minute.
3. re-design your SP/Application to live with this SP not-running every minute (ie ... run and update/report on all records since the last run)
January 31, 2007 at 6:07 am
losts of good suggestions here...I wanted to mention that you should look at what the jobs are doing as well....maybe they can be replaced outright.
I had a similar issue where a job would update a static table with totals and such....it would take a couple of minutes to update the "static" table with the data it was pulling together for reporting purposes.
the solution was to create a view that contained the data we were looking for...it's big, with lots of sub selects and joins, but it allowed me to eliminate a proc that ran for a couple of minutes.
Lowell
February 19, 2007 at 1:10 pm
Another thought would be to break the job into more than one job. The stored proceedure that can run more than a minute could be put into it's own job that is run less often and the other tasks could be run once a minute. Of course, it all depends on the inter-dependancy of the tasks you are trying to run.
Steve
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply