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 Max_Queue_Readers
property.
Original product version: SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2
Original KB number: 3163368
Symptoms
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_Readers
property 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 Max_Queue_Readers
.
Cause
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 isn't deleted because its reference count hasn't 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.
Workaround
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)
More information
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.
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for