Interesting Behavior Of Service Broker Queues

Hello All:

I've been experimenting with the new SQL Server Service Broker, and I think I've discovered some interesting behavior.  Service Broker relies on "Queues" to store messages that need to be processed.  Service Broker operates by sending a message from one Queue (the INITIATOR Quque) to another Queue (the TARGET Queue).  A Queue can have an "Activation Stored Procedure" associated with it.  This procedure is what actually processes the messages in the Queue. 

The first behavior I obeserved related to the setting of a Queue's RETENTION parameter.  The RETENTION parameter indicates whether or not the Queue will retain a copy of the messages it receives.  By default, the parameter's value is "OFF" (meaning it will not retain messages).  In the Activation Stored Procedure of my TARGET Queue, I used "sp_send_dbmail" to send an e-mail message.  I wanted to capture the "conversation_handle" (a uniqueidentifier that identifies a particular message) and include it in the body of the e-mail.  I was unable to capture it, because the Queue's RETENTION parameter was "OFF".  When I tried to capture the conversation_handle from the INITIATOR queue (whose RETENTION parameter was "ON"), I was successful.  The moral of the story is you apparently need to have RETENTION = "ON" if you need to capture information from a Queue.

The second behavior I observed relates to the setting of a Queue's MAX_QUEUE_READERS setting.  This setting allows you to automatically invoke multiple instances of the Activation Stored Procedure.  As messages come into the Queue, the Queue creates an additional instance of the Activation Stored Procedure up to the number you specified in the MAX_QUEUE_READERS setting.  This allows parallel processing of messages in the Queue.  There is also a programming technique called a "RECEIVE LOOP" which is used for processing messages.  In the RECEIVE LOOP, you have a parameter called WAITFOR which tells the Queue whether it should stay on constant alert for new messages or whether it should time out after a specified number of seconds.

If you have the Queue wait indefinitely (by not specifying a TIMEOUT value in the WAITFOR statement) and you have invoked multiple copies of the Activation Stored Procedure, the procedure will remain in memory indefinitely.  Therefore, if you make a change to the code of the Activation Stored Procedure, the change will NOT be reflected in the Activation Stored Procedure until you change the STATUS of the Queue.  I had changed my procedure so that it would not send an e-mail, but the e-mails kept coming.  The e-mails did not stop until I executed an ALTER QUEUE statement.  I ran "ALTER QUEUE queue_name WITH STATUS = OFF;" and then I ran "ALTER QUEUE queue_name WITH STATUS = ON;"  After that, the changes were reflected in the procedure.

Be aware of this behavior as you design your Queues.