My Adventures in Coding

November 22, 2007

SQL Server: Service Broker Tips and Tricks

Filed under: SQL Server 2005 — Brian @ 8:31 am

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'

Useful Resources

How to troubleshoot service broker problems

12 Comments »

  1. 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!

    Comment by Freight Broker Training — September 3, 2010 @ 1:24 pm | Reply

  2. 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.

    Comment by mbourgon — May 30, 2012 @ 8:32 pm | Reply

    • Great, I am glad the info helped!

      Comment by Brian — May 30, 2012 @ 8:36 pm | Reply

  3. Great explanation ..

    Comment by rhrhh — June 22, 2012 @ 4:16 am | Reply

  4. This is an excellent reference, thank you for sharing.

    Comment by sa — October 2, 2015 @ 11:56 am | Reply

  5. Thanks

    Comment by brahma — April 6, 2016 @ 9:15 am | Reply

  6. […] SQL Server: Service Broker Tips and Tricks | My … – Nov 22, 2007  · 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 … […]

    Pingback by How To Check If Sql Service Broker Is Enabled | CamHR — May 31, 2016 @ 5:51 pm | Reply

  7. 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.

    Comment by Ranga N — June 24, 2016 @ 9:18 am | Reply

    • 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.

      Comment by Brian — June 24, 2016 @ 11:38 am | Reply

  8. 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.

    Comment by Ranga N — June 24, 2016 @ 12:27 pm | Reply

  9. […] SQL Server: Service Broker Tips and Tricks | My … – Nov 22, 2007  · 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 … […]

    Pingback by Sql Tips And Tricks 2009 | bokalo — August 12, 2016 @ 6:36 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: