These days, I am spending some time with Service Broker, testing various features exposed by SB. Many times, I am finding errors (due to my bad code) which results in dialog-conversations that are not closed, messages that are not processed etc. So, before testing something new, I needed a way to do a 'clean up' operation.
I wrote a stored procedure that iterates over all the open conversations and closes the conversation. Here is the code.
CREATE PROCEDURE CleanBrokerConversations
-- create a memory table. I dont prefer cursors usually
DECLARE @t TABLE( AutoID INT IDENTITY, ConversationHandle UNIQUEIDENTIFIER)
-- insert the handles of all open conversations to the
-- memory table
-- local variables
DECLARE @cnt INT, @max INT, @handle UNIQUEIDENTIFIER
SELECT @cnt = 1, @max = COUNT(*) FROM @t
-- run a loop for each row in the memory table
WHILE @cnt <= @max BEGIN
-- read the conversation_handle
@handle = ConversationHandle
FROM @t WHERE AutoID = @cnt
-- end conversation
PRINT 'Closing conversation: ' + CAST(@handle AS VARCHAR(50))
END CONVERSATION @handle WITH CLEANUP
-- increment counter
SELECT @cnt = @cnt + 1
Closing conversations one-by-one may take very long if you have a large number of open conversations. If you have a few thousands of conversations, it may take a couple of minutes or more. There is another quick and dirty way of cleaning up all open conversations.
ALTER DATABASE BrokerTestDB SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
This will clear all the open conversations much faster. However, I think this is advisable only on test environments where you need a quick way to cleanup the messages and open conversations. On production environment, it is always advisable to do close the conversations one-by-one, gracefully.