April 13, 2009 at 3:17 pm
I have a very simple job that executes a stored proc on a fixed 1 minute interval throughout the day. Sometimes, the SP will take 5 minutes to complete causing the next runtime for the job to be 5 minutes later and thus missing 4 calls.
Is it possible to have the SQL Agent spawn multiple instances of a job even when one hasn't finished executing? I've tried this using an IS package as well, but to no avail (the agent will wait until completion before it spawns another process).
Anyone know if there is a way around this? Thanks in advance.
April 14, 2009 at 7:51 am
But dont u think if u have multiple processes running at the same time, u will face resource contention - locking\blocking.
I would suggest u leave it as is or use service broker to achieve your desired solution.
April 14, 2009 at 8:10 am
Can you describe roughly why you need to run the SP so frequently and give us an idea what it does. Maybe someone here will have other ideas about how to achieve the required outcome.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 14, 2009 at 8:17 am
i bet you could always do a custom scheduler check (implement custom queuing with a table to log the execution status and started and finished and check on every run if there is not a previous existing execution)...but those were the days of SQL 2000, and i did implement one such 'watchdog' program 🙂
April 15, 2009 at 12:50 pm
I wanted to thank everyone for their replies. After some research, i've decided to implement an SSB solution. Thanks again for your help!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply