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:

use master

if DB_ID('ServiceBrokerTest') is not null

drop database ServiceBrokerTest

go

create database ServiceBrokerTest

go

alter database ServiceBrokerTest set enable_broker

go

use ServiceBrokerTest

go

create procedure ActivationProc

as

begin

set nocount on

set xact_abort off

begin transaction

declare @handle uniqueidentifier = null, @message_type sysname = null

declare @ExecutionEngineLogID int

waitfor(

receive top(1)

@message_type = message_type_name,

@handle = conversation_handle

from Queue1

), timeout 10;

if @handle is null

begin

goto ___Quit___ -- can do nothing if handle is empty

end

if @message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'

or @message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error'

begin

end conversation @handle;

goto ___Quit___

end

if @message_type = 'DEFAULT'

begin

commit

waitfor delay '00:00:20'

end

___Quit___:

if @@trancount > 0

commit

end

go

create queue Queue1 with status = on , retention = off , activation ( status = on , procedure_name = ActivationProc, MAX_QUEUE_READERS = 10 , EXECUTE AS N'dbo' )

GO

create service Service1 on queue Queue1 ([DEFAULT])

GO

create procedure SendCommand

as

begin

set nocount on

declare @handle uniqueidentifier

select @handle = conversation_handle

from sys.conversation_endpoints

where is_initiator = 1

and far_service = 'Service1'

and state <> 'CD'

if @handle is null

begin

begin dialog conversation @handle

from service Service1

to service 'Service1'

with encryption = off;

end;

send on conversation @handle message type [DEFAULT](0x01);

end

GO

set nocount on

go

exec SendCommand

go 200

--- run this few times until you see 10 activation procedure instances

select * from sys.dm_broker_activated_tasks

go

alter queue Queue1 with status = on , retention = off , activation ( status = on , procedure_name = ActivationProc, MAX_QUEUE_READERS = 5 , EXECUTE AS N'dbo' )

go

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

go

select * from sys.dm_broker_activated_tasks -- 10 threads will stay there for a while

/*

--clean up

use master

alter database ServiceBrokerTest set single_user with rollback immediate

drop database ServiceBrokerTest

*/