In using Service Broker Queues for the last few months I have come up with a list of very handy frequently used queries.
Is Service Broker Enabled?
SQL Server has a database wide setting that allows all service broker queues in the database to be disabled by setting a single flag. The problem is if a database is moved or restored, this flag is automatically reset to false. There is no way to override this setting. The problem is all messages that are sent to queues while service broker is disabled, will be lost, because this setting disables receiving of messages on all queues.
Check if service broker is enabled
SELECT is_broker_enabled FROM sys.databases WHERE name = 'MyDatabaseName'
Enable service broker
--Waits for all open transactions to finish before making change ALTER DATABASE MyDatabaseName set NEW_BROKER OR --Rolls back all other transactions instead of waiting ALTER DATABASE MyDatabaseName set NEW_BROKER WITH ROLLBACK IMMEDIATE
Those commands work fine on a local machine, but if you need to enable service broker on a database where others have open connections (e.g., query window), you will need the following:
ALTER DATABASE myDatabase set single_user with rollback immediate GO ALTER DATABASE myDatabase set new_broker GO ALTER DATABASE myDatabase set multi_user
Activate/Enable a Queue
Activation on a queue allows the attached stored procedure to be run anytime a new message is inserted into the queue. (Note: I am also ensuring the queue is enabled in this update statement). If a queue is not enabled, it will not accept messages, so any messages sent to the queue will be lost!
Enable activation on the queue to process messages
ALTER QUEUE dbo.MyQueue WITH STATUS = ON , RETENTION = OFF , ACTIVATION ( STATUS = ON )
Disable activation on the queue to stop processing messages
ALTER QUEUE dbo.MyQueue WITH STATUS = ON , RETENTION = OFF , ACTIVATION ( STATUS = OFF )
Service Broker General Queue Information
This query gives you all information about each queue in the database. Use this query to find out if a queue is enabled, if activation is enabled on the queue, etc.
select * from sys.service_queues
Viewing Messages on a Queue
If you need to view the body of messages on a service broker queue, you can use this query to return the XML content of each message on the queue.
SELECT cast(Message_Body as XML) FROM dbo.QueueName with(nolock
Count Messages on a Queue
You can use “Select count * from dbo.QueueName”, however that is slow, especially if there are thousands of messages presently on the queue. It is much faster to use the message count that service broker stores on each queue.
select p.rows from sys.objects as o join sys.partitions as p on p.object_id = o.object_id join sys.objects as q on o.parent_object_id = q.object_id where q.name = 'queuename' and p.index_id = 1
Get the SPID of a Running Activation Sproc
When an acitvation sproc is running on prod and causing problems, it can be difficult to figure out which SPID is associated with that sproc when we need to kill that process. The following query makes that easy to find:
select * from sys.dm_broker_activated_tasks
Find out of a conversation has been ended
If you ever get this error message, then you have a transaction that has not been ended.
Msg 8429, Level 16, State 1, Line 46
The conversation endpoint is not in a valid state for SEND. The current endpoint state is ‘DI’.
DI means a “Disconnected Inbound” which means the other end of the conversation has been ended with an END CONVERSATION command, however this side of the conversation has been left open. To check for unended conversations use the following command:
select * from sys.conversation_endpoints
To close the conversation get the conversation_handle from the above command then run an END CONVERSATION on that conversation handle. Here is an example:
END CONVERSATION '73645350-72C4-DC11-9374-0003FF3A5C87'