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.
If you like this article, do like “Extreme-Advice” page in Facebook.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.
Deal with message queue conversation with state CD in Service Broker http://t.co/Dl1sqSkiOF