Max_Queue_Readers property is ignored when you try to limit activation tasks in Service Broker

Symptoms
Consider the following scenario:
  • You use Service Broker in Microsoft SQL Server 2014 or Microsoft 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 multiuser 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 co-exist 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.
Properties

Article ID: 3163368 - Last Review: 05/19/2016 18:12:00 - Revision: 1.0

Microsoft SQL Server 2016 Developer, Microsoft SQL Server 2016 Enterprise, Microsoft SQL Server 2016 Standard, Microsoft SQL Server 2014 Developer, Microsoft SQL Server 2014 Enterprise, Microsoft SQL Server 2014 Enterprise Core, Microsoft SQL Server 2014 Business Intelligence, Microsoft SQL Server 2014 Standard, Microsoft SQL Server 2012 Developer, Microsoft SQL Server 2012 Enterprise, Microsoft SQL Server 2012 Enterprise Core, Microsoft SQL Server 2012 Standard, Microsoft SQL Server 2008 R2 Datacenter, Microsoft SQL Server 2008 R2 Developer, Microsoft SQL Server 2008 R2 Enterprise, Microsoft SQL Server 2008 R2 Standard, Microsoft SQL Server 2008 R2 Workgroup

  • kbprb kbsurveynew kbexpertiseadvanced KB3163368
Feedback