sys.conversation_endpoints build up
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.
BEGIN
BEGIN TRY
SET NOCOUNT ON;
DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER, @RecvReqMsg VARBINARY(MAX), @RecvReqMsgName sysname;
BEGIN TRANSACTION;
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'
BEGIN
DECLARE @SBMsgXML XML = CAST(@RecvReqMsg AS XML);
EXEC SomeDB.dbo.InserRecordSB @SBMsgXML;
END
ELSE IF @RecvReqDlgHandle IS NOT NULL AND @RecvReqMsgName = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @RecvReqDlgHandle
END
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
Please help
Software/Hardware used:
SQL 2008 r2, Service Broker
Software/Hardware used:
SQL 2008 r2, Service Broker
ASKED:
September 23, 2011 11:40 PM
UPDATED:
March 31, 2012 1:16 AM
Answer Wiki:
There's nothing wrong with Service Broker.
Are you doing an END CONVERSATION on the sending side as well? Service Broker requires a two phase commit to end the conversation. You should have an activated stored procedure on the sending queue on the sending server which closed the conversations when messages arrive in the sending queue.
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
