It Seems that there is a lot of problems with SB cleanup? I have SQL 2008 r2 running on two different servers. The target system processes the millions of records in the queues but will not clean out the sys.conversation_endpoints, or the sys.conversation_endpoints tables.
This is part of the activation proc that is run on the target server.
SET NOCOUNT ON;
DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER, @RecvReqMsg VARBINARY(MAX), @RecvReqMsgName sysname;
WAITFOR ( RECEIVE TOP(1) @RecvReqDlgHandle = conversation_handle, @RecvReqMsg = message_body, @RecvReqMsgName = message_type_name
FROM AccountDataQueue), TIMEOUT 1000;
IF @RecvReqDlgHandle IS NOT NULL AND @RecvReqMsgName = N'IPGServiceBroker_MT'
DECLARE @SBMsgXML XML = CAST(@RecvReqMsg AS XML);
EXEC SomeDB.dbo.InserRecordSB @SBMsgXML;
ELSE IF @RecvReqDlgHandle IS NOT NULL AND @RecvReqMsgName = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
END CONVERSATION @RecvReqDlgHandle
Software/Hardware used: SQL 2008 r2, Service Broker
Free Guide: Managing storage for virtual environments
Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!