SQL Server: Service Broker Tips and Tricks

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 ENABLE_BROKER
OR
--Rolls back all other transactions instead of waiting
ALTER DATABASE MyDatabaseName set ENABLE_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 switch to single user mode, before you can make the change:

ALTER DATABASE myDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE myDatabase SET ENABLE_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 start processing 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 if 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'

Useful Resources

How to troubleshoot service broker problems

declare @conversation uniqueidentifier while exists (select 1 from dbo.MyQueue ) begin set @conversation = (select top 1 conversation_handle from dbo.MyQueue ) end conversation @conversation with cleanup end

Freight Broker Training 1:24 pm on September 3, 2010

Very informative site. Do happen to know any site that includes free download of the latest SQL? I’ll be waiting for your reply. Thanks!
mbourgon 8:32 pm on May 30, 2012

Thanks for the info on the sys table for SB objects. We ran into a problem where after a server reboot one of the queues wasn’t enabled. So, I’m writing an alert to catch that next time.
- Brian 8:36 pm on May 30, 2012
  
  Great, I am glad the info helped!
rhrhh 4:16 am on June 22, 2012

Great explanation ..
sa 11:56 am on October 2, 2015

This is an excellent reference, thank you for sharing.
brahma 9:15 am on April 6, 2016

Thanks
Pingback: Service Broker – Upgrade | Romain Casteres
Pingback: How To Check If Sql Service Broker Is Enabled | CamHR
Ranga N 9:18 am on June 24, 2016

Nice reference article for SB. Is there a quick way to delete all messages in a queue instead of deleting one at a time? Sometimes thousands of messages get queued and need to cleanup.
- Brian 11:38 am on June 24, 2016
  I am not sure of a way to delete all messages in one step, but the way I have handled removing all messages from a queue was to just take the sql that processes messages from the queue and run it in a query window but with the part that does the work removed. So you are reading each message off the queue, doing nothing with it, ending the conversation, and moving to the next message.
  
  For example:
```
declare @conversation uniqueidentifier
while exists (select 1 from dbo.MyQueue )
begin
    set @conversation = (select top 1 conversation_handle from dbo.MyQueue )
    end conversation @conversation with cleanup
end
```
  I am sure there are probably better ways to do this, but this is the only one I can remember at the moment.
Ranga N 12:27 pm on June 24, 2016

I think I am looking for something like Truncate Table instead of delete one row at a time, I am learning Service Broker, so excuse my ignorance.
Pingback: Sql Tips And Tricks 2009 | bokalo
unluckyMan93 9:35 am on March 21, 2018

Still in 2018 this article helped me !! Thanks man
Andrew 2:26 pm on April 17, 2018

re: Enable service broker: You are telling people to do a NEW_BROKER. This will assign a new broker id as shown in sys.databases. When restoring a production database in to say a UAT environment I would recommend this, however if you purely want to enable broker, you just need to run ALTER DATABASE [YourDatabase] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE. By suggesting NEW_BROKER you may lead some people down a path they do not want to go down in a production environment. Once you change the broker id I do not think it’s possible to revert it, unless you do a full restore.
- Brian 7:26 pm on June 1, 2020
  
  You are absolutely right! Thanks for pointing this out. I have known this for years but did not realize my old post from 2007 was using NEW_BROKER in the example when it should have been ENABLE_BROKER. I have updated the post. Thanks!