Activated Proc not firing.

Hi There

I just had the following scenario.

I checked sys.service_queues to confirm the my queue has an activated proc assigned to it and is activation_enabled.

I send messages to the service, however i see that the messages just sit in the queue. I run profiler with all SB and Error events there is nothing, i check the sql server log, there is also nothing.

So i have no idea why the activated proc is not firing, all i see in the profiler trace is that the messages are acknowledged but the activated proc does not execute.

For a while i was stumped. i then tried to execute the proc myself and i got a syntax error.

Basically what happened was that i altered a table that the proc used and now the proc cannot execute cos there are more columns that must be inserted so i altered the proc.

However the activated proc still did not fire.

I had to disable and re-enable activation for it to work.

What i am wondering is why was there no indication in profiler or the sql og as the the fact that something was wrong with the activated proc ? Surely the activated proc should have fired and given the same error i got either to the sql log or profiler ?

And why did i have to re-enable activation ? Does sql cache the proc when you activate it and not know that the proc has changed when you alter it ?

Thanx

I had a similar issue...and I am trying to get a resolution on this. In my scenario, it was a new service broker startup (complete rebuild of queues from build scripts that were working in our development database environment)...We have about 30 queues in which all but 1 worked fine. When I went to look to see what happened, the queue was up and activation was on and the messages were sitting in the target queue. When I manually executed the activation stored procedure everything was successful and processed fine (no errors). This is not the first time this has happened, and after it is manually executed the activation seems to occur without issue from that point forward. I am just kind of concerned that when this system is in production and they have to take it down to perform maintenance, this will happen again and since nothing is really "wrong" with the queue, monitoring this is fairly difficult.

Any ideas or suggestions?

|||

When you run into this problem, look at sys.dm_broker_queue_monitors, sys.dm_broker_activated_tasks and sys.service_queues views. We need the content of these views to diagnose the issue.

HTH,
~ Remus

|||

Hi Remus

Ok next time i will get that information.

Thanx