SDU Tools: Clear Service Broker Transmission Queue in T-SQL

Service Broker is one of my favorite tools in SQL Server. So many applications need to have a transactional queue, and many people try to build them using tables and other objects. But SQL Server has Service Broker and with it, you get the beauty of using the knowledge of someone who already knows about queues having thought about how they need to work.

But while Service Broker is quite forgiving, it’s common while developing Service Broker applications to make mistakes and end up with messages in queues that will never be delivered because you had some bug in your code. Service Broker doesn’t want to ever just throw your data away.

One of our free SDU Tools for T-SQL developers and DBAs is ClearServiceBrokerTransmission queue. Let’s look at an example of using it.

We’ll create a new database called Development and use it:

Then create a master encryption key, a queue, and a service:

Now let’s send a message that will never be delivered (because the target service doesn’t exist):

We can query to see that it’s still in the transmission queue:

If we scroll to the right, we can see why it’s stuck:

Now we realize that we messed up the service name and it will never be delivered, so we want to flush the queue. We can do that easily:

Note that if you are following along with the code and don’t have SDU_Tools installed in that database, you’ll need to change the EXEC line to point to a database where they are installed.

And finally, we could just clean up: