This is another issue that was kindly exposed by our colleague, John Huang, from Canada, through the MCM community distribution list.
Once an activated stored procedure finishes its execution, Service Broker runtime code doesn’t verify whether the maximum number of queue readers has been exceeded. Therefore, when the number of queue readers is reduced via the MAX_QUEUE_READERS parameter in the ALTER QUEUE statement, if there are many messages in the queue, it could take a long time to take effect. Meaning that, as long as there are messages to process in the queue, an activated task will not deactivate even if the maximum number of queue readers has been decreased to any number lower than the number of currently activated tasks. When the queue is emptied, those activated tasks will shutdown and when new messages arrive new tasks will become activated as long as the maximum number hasn’t been surpassed.
At the time this post is published, the fix to this issue is already checked in against the branch from where the next major release of SQL Server coming after SQL Server 2012 will be build. Meanwhile, in any version ranging from 2005 up to 2012, this is the way SSB runtime behaves.
The following annotated script demonstrates how to reproduce the problem:
if DB_ID('ServiceBrokerTest') is not null
drop database ServiceBrokerTest
create database ServiceBrokerTest
alter database ServiceBrokerTest set enable_broker
create procedure ActivationProc
set nocount on
set xact_abort off
declare @handle uniqueidentifier = null, @message_type sysname = null
declare @ExecutionEngineLogID int
@message_type = message_type_name,
@handle = conversation_handle
), timeout 10;
if @handle is null
goto ___Quit___ -- can do nothing if handle is empty
if @message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
or @message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
end conversation @handle;
if @message_type = 'DEFAULT'
waitfor delay '00:00:20'
if @@trancount > 0
create queue Queue1 with status = on , retention = off , activation ( status = on , procedure_name = ActivationProc, MAX_QUEUE_READERS = 10 , EXECUTE AS N'dbo' )
create service Service1 on queue Queue1 ([DEFAULT])
create procedure SendCommand
declare @handle uniqueidentifier
select @handle = conversation_handle
where is_initiator = 1
and far_service = 'Service1'
and state <> 'CD'
begin dialog conversation @handle
from service Service1
to service 'Service1'
with encryption = off;
send on conversation @handle message type [DEFAULT](0x01);
--- run this few times until you see 10 activation procedure instances
select * from sys.dm_broker_activated_tasks
alter queue Queue1 with status = on , retention = off , activation ( status = on , procedure_name = ActivationProc, MAX_QUEUE_READERS = 5 , EXECUTE AS N'dbo' )
select * from sys.dm_broker_activated_tasks --- you will see 10 records returned
waitfor delay '00:00:30'
select * from sys.dm_broker_activated_tasks -- you will see 10 records returned here too
select * from sys.dm_broker_activated_tasks -- 10 threads will stay there for a while
alter database ServiceBrokerTest set single_user with rollback immediate