Deal with message queue conversation with state CD in Service Broker
Each side of a Service Broker conversation is represented by a conversation endpoint and sys.conversation_endpoints catalog view contains a row per conversation endpoint in the database. Generally when service broker accept record in message queue, it used to process message and remove it after closing by its own.
Recently at one of client site I have seen that messages are having “CD” state in sys.conversation_endpoints catalog view but it is not being removed from the message queue. Message queue was having approx half a million message with “CD” closed state so in order to remove those messages, I have created one small script to clean it up which might be helpful for you as well sometime.
CREATE PROCEDURE [CleanConversations] AS SET NOCOUNT ON BEGIN BEGIN TRY DECLARE @handle UNIQUEIDENTIFIER DECLARE conv CURSOR FOR SELECT a.conversation_handle FROM sys.conversation_endpoints a with(nolock) WHERE state = 'CD' OPEN conv FETCH NEXT FROM conv INTO @handle WHILE @@FETCH_STATUS = 0 BEGIN END CONVERSATION @handle WITH CLEANUP FETCH NEXT FROM conv INTO @handle PRINT @handle END CLOSE conv DEALLOCATE conv END TRY BEGIN CATCH DECLARE @msg VARCHAR(MAX) SELECT @msg = ERROR_MESSAGE() RAISERROR('Error in %s: %s', 16, 1, 'CleanConversations', @msg) END CATCH END GO
After having the above stored procedure, you can run it and clear all conversation which are already closed. Generally message should be closed and removed by its own but once in a blue moon if you come across the situation which I have observed at my client site, you can use this SP.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.