Service Broker Adventures: Stucked Queue Monitors

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

This week I’m in London helping a client with its large scale Service Broker deployment, mainly troubleshooting some strange problems with Service Broker and helping to improve the overall scalability and messaging throughput. Today I want to share a strange scenario with you, how a badly written Activated Stored Procedure isn’t really activated by Service Broker. Let’s assume the following basic setup of Service Broker, where we are sending messages from the Initiator Service to the Target Service:

For the Target Service an Activated Stored Procedure was written, and this Stored Procedure was driven by a configuration table. The configuration table specified if the Stored Procedure was retrieving messages from the underlying queue, or not. Have a look at the following code:

This approach is a little bit strange, because you just have to disable activation, which leads to the same scenario, so that the Activated Stored Procedure isn’t doing anything anymore (as soon as the queue is empty). This scenario was invented, because sometimes it was impossible to disable Service Broker queues, because of a Locking/Blocking scenario. The Locking/Blocking scenario occurred, because there were uncommitted transactions from the Activated Stored Procedure, which means that some locks were held forever in SQL Server, which finally blocked the disabling of the queue. See my last blog posting on this phenomenon:,guid,fc4f98af-f42a-4b3d-872e-c31815e6fc02.aspx. So when you have changed the entry in the config table, the Activated Stored Procedure was just exiting – so far so good in the theory…

The real truth about that “solution” was the fact, that the Activated Stored Procedure wasn’t ever processing messages. You might now ask why. The answer on that question was finally very easy: As soon as your Stored Procedure gets activated by Service Broker, you MUST process messages from your queue through the RECEIVE statement, otherwise Service Broker assumes that your Stored Procedure has encountered a problem, and considers the Stored Procedure to be failed. See the following remarks from Books Online (

An activated stored procedure must receive messages from the queue that activated the procedure. If the stored procedure exits without receiving messages or the queue monitor detects that the stored procedure is not receiving messages after a short time-out, the queue monitor considers the stored procedure to have failed. In this case, the queue monitor stops activating the stored procedure.

But how can you now troubleshoot that specific problem to find out if Service Broker had considered your Stored Procedure to be failed? There’s the DMV sys.dm_broker_queue_monitors, which shows the so-called Queue Monitors. Those components are responsible for activating your Stored Procedure. A Queue Monitor can be in 3 different states:


When a Stored Procedure is not activated for your queue, then the corresponding Queue Monitor is in the INACTIVE state. As soon as the Queue Monitor has started your Stored Procedure, the Queue Monitor goes into the NOTIFIED state. And finally the Queue Monitor goes into the RECEIVE_OCCURING state, when the Activated Stored Procedure receives messages. This means that the Queue Monitor remains in the NOTIFIED state, when your Stored Procedure isn’t receiving any messages. As long as your Queue Monitor is stucked in the NOTIFIED state, you are not processing any messages from your queue!

In this specific scenario the Queue Monitor is also not moving into the RECEIVE_OCCURING state, when you change the entry in the user-defined configuration table – it just remains in the NOTIFIED state. The only solution is to disable and re-enable the queue to restart the corresponding Queue Monitor. But when your queues are blocked through locks from an uncommitted transaction… you see one problem leads to another problem… You can find here the download to the script with which you can reproduce the stucked Queue Monitor within Service Broker.

What’s the moral of this story: read the f… manual and code your Activated Stored Procedures in a very robust way J. I’ve talked a lot to different people over the last years about Service Broker. Everyone is just scared about Service Broker, because it’s such an overcomplicated technology. In my opinion Service Broker isn’t really complicated, but you have to know the various design patterns behind Service Broker, and it’s up to your Activated Stored Procedures what you’re doing with Service Broker, and how healthy your Service Broker solution will be. Service Broker itself is very robust (he’s just sending messages from A to B, nothing more), and will not cause any problems to you, almost of the time you are the trigger of the problems…

Thanks for reading



Copyright © 2015 by SQLpassion · Klaus Aschenbrenner · Imprint · Offerings · Academy · Contact · Go to Top