Service Broker is SQL Server’s internal messaging system. It has been designed as an asynchronous, reliable and transactional messaging system for inter or intra database messaging, or even inter SQL instance messaging.

It is a powerful tool for decoupling extended transactions across time and across databases. It is designed to be reliable across databases stopping and starting, network outages, server migrations and database backups and restores.

It is also a complex configuration. I wanted to prove to myself that message reliability would persist across database restores so I set up a test which I intend to post to this blog at a later date. Meanwhile in trying to configure the test I learnt and used a number of trouble shooting tips.

So what does a DBA need to know regarding service broker?

Catalog views:

Inter SQL instance configurations use endpoints :

select * from sys.service_broker_endpoints

There is a send queue and a receive queue and they communicate using conversations. The send and receive queues are associated with services and are database scoped objects (these catalog views are also database scoped):

-- one row per endpoint of service broker conversation 
--in current database
select * from sys.conversation_endpoints 
--one row per queue in current database
select * from sys.service_queues 
--transmission queues are internal service broker 
--queues for storing messages during delivery
select * from sys.transmission_queue

Queues are implemented as internal tables that can be viewed with:

select Q.name as queuename, i.name as internalname
from sys.service_queues as Q
join sys.internal_tables as I
on q.object_id = i.parent_object_id

Conversation population explosion:

Conversations need to be closed with the END CONVERSATION command. Conversation endpoints are cached in memory and backed by storage in tempdb, so incorrectly handled conversations can build up un-closed endpoints and ultimately starve SQL Server of memory and disk space. (Conversation endpoints are designed to persist at the target for half an hour or so to prevent a known exploit involving sending duplicate messages)

The fastest way to clear all conversations in a given database is with:

alter database <database name> set new_broker

If you want to be a bit more discerning you could add some filters to this base code (this needs to be run at the sender and receiver):

use <database name>
go
declare @h uniqueidentifier
while exists(select 1 from sys.conversation_endpoints)
begin
set @h = (select TOP(1)[conversation_handle]  from sys.conversation_endpoints)
end conversation @h
end

Poison messages:

If a message delivery results in an error the message is popped back on the queue and the receiving transaction is rolled back. If the error is not correctly handled then message delivery will fail again, the message will be popped back on the queue, the transaction rolls back…

This can result in an infinite loop and is called the poison message problem. SQL Server service broker defaults to disabling the queue if five message ‘rollbacks’ in a row are detected.

Backup and restore:

When a database is backed up all service broker objects are backed up too, including queues.

By default, when a database is restored it is restored with service broker disabled (although the identifying service broker GUID is maintained). To handle service broker during the restore process three WITH options are provided for the T-SQL RESTORE command.

ENABLE_BROKER: Service broker is enabled after the restore completes and message delivery can resume immediately. The original GUID is retained.

ERROR_BROKER_CONVERSATIONS: All service broker conversations are ended with an error message, allowing applications to handle cleanup. The original GUID is retained.

NEW_BROKER: Service broker uses GUIDs to identify services. These GUIDs must be unique within the SQL Server instance. If you are restoring a database to a different SQL Server instance you can guarantee a unique GUID by using the NEW_BROKER option. This essentially creates a new service and all conversations are ended with no error messages.

The ssbdiagnose utility:

Service broker is tricky to configure, and equally tricky to debug. SQL Server 2008 ships with a command line tool ssbdiagnose that can scan service broker configurations and report issues. The tool can run in CONFIGURATION mode and check the service broker objects and configuration, or it can run in RUNTIME mode and trace and monitor active conversations.

Full details of all the switches and syntax for the tool are in BOL or here, but by way of example here is the command line I used to check my configuration that used two databases on a single SQL Server instance (‘sender’ and ‘receiver’ are service names unique to my configuration):

ssbdiagnose -E CONFIGURATION 
FROM SERVICE sender 
-S <machine name> 
-d <sender database> 
TO SERVICE receiver 
-S <machine name> 
-d <receiver database>

Conclusion: The intention of this post was to share some of the tips and tools that I have been using to troubleshoot service broker. Hopefully they are of use.