开发者

Service Broker : Sys.Conversation_endpoints filling up with CO/CONVERSING messages when using With Cleanup

开发者 https://www.devze.com 2023-01-11 10:37 出处:网络
We recently identified a problem with one of our databases where as a result of a \'fire & forget\' setup (i.e: conversations being closed immediately after sending), our sys.conversation_endpoint

We recently identified a problem with one of our databases where as a result of a 'fire & forget' setup (i.e: conversations being closed immediately after sending), our sys.conversation_endpoints table was filling up with DI/DISCONNECTED_INBOUND messages. This eventually spilled over into the tempDB, causing it to grow enormously and eat up precious disk space. We eventually resolved this issue by commenting out the line

END CONVERSATION @handle WITH CLEANUP

in our sending SP and closing the conversations in our receiving SP using the same code,

END CONVERSATION @handle WITH CLEANUP

However, we now have a new issue. Since moving servers (and migrating from SQL Server 2005 to SQL Server 2008) we've recently discovered that sys.conversation_endpoints is now filling up with CO/CONVERSING messages, indicating that the conversations are not being closed. The receiving SP is closing them, or at least is running the command to do so, so I don't understand where these messages are coming from.

I've tried going back to ending the conversation at the point of send, but it has no effect. Is it wrong to end conversations on the receiving end using WITH CLEANUP? Or is there some other problem?

This post on techtarget seems to suggest its a bug, and that running a job to cleanup the leftovers is the only solution...

UPDATE: Pawel pointed out below that I should be avoiding the Fire & Forget Pattern, and I've added an activated SP to the initiator queue to end any conversations. However, sys.conversation_endpoints is STILL filling up, this time with CD/CLOSED messages. Here's the structure of my queues

Send_SP:

DECLARE @h UNIQUEIDENTIFIER 
BEGIN DIALOG CONVERSATION @h 
FROM SERVICE 'InitiatorQueue' TO SERVICE 'TargetQueue' 
ON CONTRACT 'MyContract' WITH ENCRYPTION = OFF; 
SEND ON CONVERSATION @h MESSAGE TYPE 'MyMessage' (@msg)

Receive_SP (Activated SP on TargetQueue)

DECLARE @type SYSNAME, @h UNIQUEIDENTIFIER, @msg XML;
DECLARE @target TABLE (
    [message_type_name] SYSNAME,
    [message_body] VARBINARY(MAX),
    [conversation_handle] UNIQUEIDENTIFIER
)
WHILE(1=1)
BEGIN TRANSACTION
    WAITFOR(RECEIVE TOP (1000) 
        [message_type_name],[message_body],[conversation_handle] 
        FROM TargetQueue INTO @target), TIMEOUT 2000            
    IF(@@rowcount!=0)
    BEGIN
        WHILE((SELECT count(*) FROM @target) > 0) 
        BEGIN
        SELECT TOP (1) @type = [message_type_name],
            @msg = [message_body],
            @h = [conversation_handle]  FROM @target;
        // Handle Message Here
        END CONVERSATION @h; 
        DELETE TOP (1) FROM @target;
    END
END
COMMIT TRANSACTION;

End_SP (Activated SP on InitiatorQueue)

DECLARE @type SYSNAME, @h UNIQUEIDENTIFIER, @msg XML;
DECLARE @init TABLE 开发者_开发知识库(
    [message_type_name] SYSNAME,
    [message_body] VARBINARY(MAX),
    [conversation_handle] UNIQUEIDENTIFIER
)
WHILE(1=1)
BEGIN TRANSACTION
    WAITFOR(RECEIVE TOP (1000) 
        [message_type_name],[message_body],[conversation_handle] 
        FROM InitiatorQueue INTO @init), TIMEOUT 2000           
    IF(@@rowcount!=0)
    BEGIN
        WHILE((SELECT count(*) FROM @init) > 0) 
        BEGIN
        SELECT TOP (1) @type = [message_type_name],
            @msg = [message_body],
            @h = [conversation_handle]  FROM @init;
        END CONVERSATION @h; 
        DELETE TOP (1) FROM @init;
    END
END
COMMIT TRANSACTION;


Using the fire-and-forget pattern will inevitably lead to this and other types of issues. Additionally it will make any hypothetical errors go unnoticed. Is there any reason why you can't change the message exchange pattern so that the target issues END CONVERSATION (without cleanup!) once it receives a message and then the initiator only calls END CONVERSATION (again, without cleanup) upon receiving end conversation message from the target?

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号