- 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
alter database <dbname> set multi_user
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.
- 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)
select * from sys.dm_broker_activated_tasks where queue_id = <queue number>
Select * from sys.dm_broker_queue_monitors where queue_id = <queue number>
Article ID: 3163368 - Last Review: May 19, 2016 - Revision: 1