SSB FAQ (4) – Debug tips for SSB beginners

SSB FAQ (4) – Debug tips for SSB beginners

There are some tips which are helpful for SSB beginners to debug and resolve some general issues.

1. sys.transmission_queue

This catalog view contains a row for each message in the transmission queue.

Select * From sys.Transmission_queue

transmission_status

The reason this message is on the queue. This is generally an error message explaining why sending the message failed. If this is blank, the message hasn’t been sent yet.

2. sys.conversation_endpoints

Each side of a Service Broker conversation is represented by a conversation endpoint. This catalog view contains a row per conversation endpoint in the database

会话端点代表 Service Broker 会话的每一端。对于数据库中的每个会话端点，此目录视图相应地包含一行。

Select * From sys.conversation_endpoints

3. Check [Initiator & Target ServiceQueue]

SELECT CAST(message_body as XML), * FROM [InitiatorServiceQueue]

SELECT CAST(message_body as XML), * FROM [TargetServiceQueue]

Just like general SELECT script to TABLE object, using the above SELECT statement is helpful to check what messages exist in Initiator & Target Queue respectively.

4. How to clean up sys.transmission_queue

Generally due to you did something wrong, such as misspell your services, or forget to create a master key, which result in your messages not being delivered, then your sys.transmission_queue will fill up.

The following script can help you clean up these messages, but it will end up all conversation in the sys.transmission_queue. So you should be very careful and think twice when you want to run it in the production environment.

declare @conversation uniqueidentifier

while exists (select 1 from sys.transmission_queue )

begin

set @conversation = (select top 1 conversation_handle

from sys.transmission_queue )

end conversation @conversation with cleanup

end

Reference URL:

1. SimonS' SQL Server Stuff ,

http://www.sqljunkies.com/WebLog/simons/archive/2006/09/28/Service_Broker___Clean_up_your_transmission_queue.aspx