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
-