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
AS
BEGIN
    BEGIN TRY
        -- 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
        INSERT INTO 
            @t (ConversationHandle)
        SELECT 
            [conversation_handle] 
        FROM
            sys.conversation_endpoints
            
        -- 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
            SELECT
                @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
        END
        
    END TRY
    BEGIN CATCH
        PRINT ERROR_MESSAGE()
    END CATCH
END

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.