Service Broker - Clean up your transmission queue

This isn't about changing your air filter, spark plugs or oil. Its about what to do when, like me, you don't get things right first time.

If developing Service Broker applications and you misspell your services, forget to create a master key, or do any of the many things that result in your messages not being delivered, then your transmission queue will fill up.

Do to the nature of SB errors don't occur at the client but rather your messages go in to the black box and are processed. If they fail they stay in the black box. Some errors can be resolved, i.e. missing services, however others can't (or are more awkward) i.e. you've misspelt something, or not used the correct security.

To clear your transmission queue you need to end the conversations, this is a handy little script for doing this. Be aware this ends ALL conversations in the queue and so should NEVER be used on a production system.

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



-
Published 28 September 2006 12:59 by simonsabin

Comments

03 October 2006 21:42 by Adam Machanic

# re: Service Broker - Clean up your transmission queue

Hi Simon,

Another way to accomplish the same thing is to do:

ALTER DATABASE n
SET NEW_BROKER

However, this gives your DB a new broker identifier.  I think that might break some functionality having to do with SSB endpoints, as I recall that something in that stack does require that you register the identifier.  I believe this will not hurt anything if you're not doing endpoints, though.