May 16, 2010 at 2:01 am
Hi,
I'm running a proc that monitor my server every 2 min by sql agent job.
this proc duration is about 2 sec.
now i want in this 2 sec to call another proc and this another proc runs about 3 min(this new poc will not run every 2 min).
how i can make this 2 sec proc to call another proc that runs 3 min and still be in the cycle to run every 2 min by sql agent job.
can i call this new proc outside the 2 min proc or something like that?(i don't want to create sql agent job for this new proc)
THX.
May 16, 2010 at 2:42 am
One option:
Determine the condition when the 3min-sproc needs to be called either within your 2se-sproc or in the next step in the job agent. Based on that condition fire a ServiceBroker event that will call your 3min sproc.
May 16, 2010 at 2:50 am
ok.
how can i do the service broker event to call this sp?
THX
May 16, 2010 at 3:04 am
Mad-Dog (5/16/2010)
ok.how can i do the service broker event to call this sp?
THX
Nope, it's the other way around: you SEND a message to the Service Broker queue and within the RECEIVE statement of the Service Broker message you call your 3min-sp.
Wouldn't it be easier to run that 3min-sp independent of the 2min scheduled sp, e.g. by running a separate job? Or can the 3min-sp improved to prform in less than 2min?
May 16, 2010 at 3:18 am
the call to the proc must be run from within the 2 min job.
is this is the only way to achieve my goal?
can call this proc from the 2 min proc with another spid maybe or something like that?
May 16, 2010 at 3:21 am
May 16, 2010 at 9:32 am
I am not sure I follow this - you want to start a process that takes 3 minutes to run, every 2 minutes? That means the previous execution will still be running when your 2 minute job runs again.
You cannot do that with an agent job - since the next time you want it to run it will already be running and the job won't be started.
Service Broker is probably the only way you are going to be able to do this, unless you can improve the 3 minute process to less than the 2 minute cycle of the first job.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
May 16, 2010 at 1:25 pm
i wrote that i want to fire the 3 min proc inside the 2 min schedule sql agent job but the proc will not interfere with the 2 min sql agent job,just call it and continue with the 2 min sched.
how i can setup the service broker to achieve what i want?
THX
May 16, 2010 at 6:48 pm
Mad-Dog (5/16/2010)
i wrote that i want to fire the 3 min proc inside the 2 min schedule sql agent job but the proc will not interfere with the 2 min sql agent job,just call it and continue with the 2 min sched.how i can setup the service broker to achieve what i want?
THX
So, you want your 3 minute job to start every 2 minutes. Then you will have multiple 3 minute jobs running at the same time ?
May 17, 2010 at 6:19 am
Can you explain what the 3 min proc is doing? I think a lot of the comments above mine are leading towards checking to see that the 3 min proc is optimized and really needs to take 3 min.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply