Max_Queue_Readers property is ignored when you try to limit activation tasks in Service Broker
This article helps you resolve the problem that occurs when more activation tasks run in Service Broker than the limit set by the
Original product version: SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2
Original KB number: 3163368
Consider the following scenario:
You use Service Broker in SQL Server 2017 on Windows, Microsoft SQL Server 2014 or SQL Server 2012.
You set Service Broker for asynchronous stored procedure execution.
You set the
Max_Queue_Readersproperty to a specific value for the Service Broker queue to limit how many instances of an activation stored procedure run at the same time.
In this scenario, you notice that more activated tasks are running than the value that's set for
This problem can occur if the Service Broker database is switched from single-user mode (
RESTRICTED_USER) to multi-user mode (
MULTI_USER) by running the following:
alter database <dbname> set multi_user
When the user mode is changed on the database, Service Broker is shut down and restarted. During this process, the existing
QueueMonitor object is dropped, and another instance of
QueueMonitor object is created. If the activation process is running a long operation while Service Broker is shutting down, the status of the
QueueMonitor object is changed to dropped.
However, the existing
QueueMonitor object instance is not deleted because its reference count has not reached zero. If the activation procedure is still running when Service Broker restarts, the new instance of the
QueueMonitor object and the dropped
QueueMonitor object will coexist in the same queue. The dropped
QueueMonitor object instance will be deleted the next time that Service Broker starts.
To work around this issue, make sure that you run
alter database [dbname] set multi_user when no activated procedure is running. To do this, use one of the following methods:
Before you change the user mode, disable all the queues in the database, and then re-enable all the queues.
Before you change the user mode, disable the activation procedure for all the affected queues by running the following command, and then re-enable the activation procedure:
alter queue <queueName> with activation (status = off)
You can check the number of activation procedures that are running for a specific queue by running a query against
sys.dm_broker_activated_tasks as follows:
select * from sys.dm_broker_activated_tasks where queue_id = <queue number>
You can query the state of the queue monitor by running the following query:
Select * from sys.dm_broker_queue_monitors where queue_id = <queue number>
The state of the queue monitor is displayed as dropped if the database user mode was changed.