Monday, 14 March 2011

Service Broker sys.transmission_queue clean up

If you’re developing a Service Broker application 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.
Due to the nature of service broker, errors don't occur at the client but rather your messages go in to Service Brokers sys.transmission_queue and are then subsequently processed. If they fail, they stay in the queue. Some errors can be resolved, i.e. missing services. However, others can't, or are just plain difficult. 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.

To see how many messages are in your transmission queue, use this
SELECT COUNT(1) FROM sys.transmission_queue
 
On my system i7, DB on Raid10, Log on Raid1, the following clears messages at approximately 20000/second.