Conversation Endpoints - DISCONNECTED_INBOUND Status

Mar 28 at 3:13 PM
Edited Mar 28 at 6:20 PM
Hi,
I am using SqlTableDependency in my project for 8 months. In all releases, that I've used since that I experienced uncontrolled database growth. This situation slowed down in 4.8.3 release with improved cleanup procedures, but still has place. Currently I've downloaded 5.1.0.0 ant took it under investigation. I've noticed the following unpleasant situation, that keeps going thru all releases: there is thousands of target endpoints with DISCONNECTED_INBOUND Status. those endpoints are cleaned after client application normal shutdown. But the problem is than my clients are keep working for months without restart and in my opinion this leads to uncontrolled database growth. Is whole this mechanism is intended or am I missing something in my data flow? Maybe setting the lifetime value will solve this problem?
PS: In >5.0.0.0 release there is also a new teardown option, setting it to false provides endpoints creation even if client application is not running.
Regards
Mar 29 at 12:26 PM
HI,
I continued my investigation and have a strong feeling that I am on the right way.
Here is the piece of trigger code, which is created by SqlTableDependency on source table :
ELSE BEGIN
;send on conversation @h message type[dbo_Application_1_16621e80-1c53-466c-b89c-a4ee5156c0d2/PictureFilePath] (0x)
END
                IF EXISTS(select * from sys.conversation_endpoints where conversation_handle = @h and lower(state) = 'er')
                BEGIN
                    RAISERROR ('An error has occurred on the conversation endpoints', 18, 127)
                END
                IF EXISTS(select * from sys.conversation_endpoints where conversation_handle = @h and lower(state) = 'cd')
                BEGIN
                    RAISERROR ('This conversation endpoints is no longer in use.', 18, 127)
                END     
               END CONVERSATION @h;
            END
and here is the article I found about similar case : http://www.davewentzel.com/content/service-broker-demystified-closed-conversations
Should the ending be done rather that way?
I am really thinking that database growth is caused by those abandoned endpoints, In my database I have about 100000 new conversations daily. Without cleaning this gives about 20GB of wasted space in DB monthly.
Am I digging in right direction ? Or is it a completely wrong way?
Coordinator
Mar 30 at 7:34 AM
HI dmay3348

THANKS A LOT FOR YOUR POST, AS WELL AS THE INVESTIGATION YOU DID.

I start doing the same right now, because this problem is VERY IMPORTANT and needs to be resolved.

In the meanwhile, if you discover something, please contact me by skype or by email.

Regards
Coordinator
Apr 3 at 9:43 AM
FIXED with version 5.3

Integration test performing:

SELECT COUNT(*) FROM sys.conversation_endpoints WITH (NOLOCK) WHERE [far_service] = 'naming';

in order to check disposition of endpoint.
Coordinator
Apr 3 at 10:05 AM
I'd like to add you in Contributors page, if you do not mind.

https://tabledependency.codeplex.com/wikipage?title=Contributors

Please write me down what you wont to write about you.
Bye
Apr 3 at 10:50 AM
HI,
Unfortunately, cannot confirm the remedy, What changes You made? I thought, that at the end, the trigger code will change, but it looks almost identical. I've double checked the version of library. it' for sure a new 5.3.0.0 from Nuget. Maybe my initialization code does something wrong?
PS: I am using MS SQL 2016.
Coordinator
Apr 5 at 9:45 AM
Hi dmax

I tested on 2008 and 2014 and endpoints are removed correclty.

What I think is that you still see the OLD ones.

Please, try to remove them manually using:
declare @h uniqueidentifier, @count int = 0;
begin transaction;
while (1=1)
begin
    set @h = null;
    select top(1) @h = conversation_handle from sys.conversation_endpoints
    if (@h is null)
    begin
        break
    end
    end conversation @h with cleanup;
    set @count += 1;
    if (@count > 1000)
    begin
        commit;
        set @count = 0;
        begin transaction;
    end
end
commit
Be carefull: this script remove all endpoints. So, if you have anoher app that use SERVICE BROKER you have to add a where condition.
Apr 5 at 10:31 AM
Edited Apr 5 at 10:33 AM
HI,
Unfortunately something is wrong.
Now I am always starting the tests with clear DB, by setting NEW_BROKER WTH ROLLBACK. It clears all. Any garbage left can be deleted maually
I even wrote a branch with native Microsoft SqlDependency. Beside that it uses completely different mechanism than You (You have the trigger for starting conversation), it also creates a single conversation endpoint for message. But when conversation ends their endpoints are left with CLOSED state, and SQL server deletes it automatically, when security timer elapsed (30min by default).
Yours endpoints are still in DISCONNECTED_INBOUND state even in new 5.3.0.0 version, and only after stopping of SqlTableDependency object, the server will delete 'em. If there are a lot of endpoints the deletion takes significant amount of time.
Cannot test Your library in other versions of SQL, my development servers are only 2016.
Apr 5 at 11:03 AM
I can confirm that the conversations stay undeleted at DISCONNECTED_INBOUND state on my 2012 while SqlTableDependency is running (v 5.3.0.0). Also the first item in conversation_endpoints table, has a state=STARTED_OUTBOUND and fa_broker_instance=NULL.
Apr 6 at 10:58 AM
Edited Apr 6 at 11:28 AM
Hi,
Here is my result,
I 've digged thru Your code of SqlTableDepnedncy. In 5.3.0.0 You made following changes in waiting cycle:
 var waitforSqlScript = teardown ? "BEGIN CONVERSATION TIMER ('" + dialogHandle + "') TIMEOUT = " + timeOutWatchDog + ";" : string.Empty;
            waitforSqlScript += "DECLARE @rh UNIQUEIDENTIFIER;";
            waitforSqlScript += $"RECEIVE TOP(0) @rh = [conversation_handle] FROM {schemaName}.[{databaseObjectsNaming}];";
            waitforSqlScript += $"WAITFOR(RECEIVE TOP({processableMessages.Count}) [message_type_name], [message_body] FROM {schemaName}.[{databaseObjectsNaming}]), TIMEOUT {timeOut * 1000};";
            waitforSqlScript += "END CONVERSATION @rh WITH CLEANUP;";
This is ok ,except one thing: this locks the sys.conversation_endpoints table;
In my opinion, the reason why those conversation are still in DI state, is that the initiator side in trigger code also should be ended that way with WAITFOR RECEIVE clause even if You do not send any response back:

here is part trigger code from SqlScripts.cs
 IF @dmlType = '{12}'
            BEGIN
                BEGIN TRANSACTION

                BEGIN DIALOG CONVERSATION @h
                FROM SERVICE [{0}] TO SERVICE '{0}', 'CURRENT DATABASE' ON CONTRACT [{0}]
                WITH RELATED_CONVERSATION_GROUP = NEWID(), ENCRYPTION = OFF;

                {9}

                END CONVERSATION @h;     -- __TO BE CHANGED FOR WAITING FOR__
                IF @@TRANCOUNT > 0 COMMIT TRANSACTION;
            END            
Look closely into the article I've linked above:
Look at Figure 5 again. This happens when the Initiator issues the END CONVERSATION before the Target. This is a bug or design flaw. The cause is the initiator is doing END CONVERSATION before the sender. <
Apr 6 at 11:33 AM
Hi dmax3348,
That's promising. Have you tried changing the trigger and inspecting the results?
Coordinator
Apr 6 at 11:59 AM
Hi all

I cannor reproduce your issue.

I attached a video where i show my quick test

http://s000.tinyupload.com/?file_id=99177421762704744945

Can you explain me how can i reproduce this issue ?
Apr 6 at 1:42 PM
Hi Christian, there is a misunderstanding. When the SqlTableDependency is stopped, all related records from conversation_endpoints are got deleted, just like in the video you linked above. You stopped the program and the records got deleted. We agree on that. However, when the SqlTableDependency keeps running, the conversation_endpoints grows constantly, inserting a new row for every message read from the queue. It would be nice, if after the message is read/delivered the related conversation record from conversation_endpoints got delated afterwards, without stopping the SqlTableDependency. Hope this helps to understand the problem. Thanks.
Apr 6 at 1:48 PM
That's right, You are closing the testapp too fast
Coordinator
Apr 7 at 5:32 AM
Hi all!

Now It is clear how to reproduce this issue.

I will fix it during this week end.

Thanks again guys for your help!
Apr 20 at 11:14 AM
Edited Apr 20 at 11:18 AM
Hi,

I looked into this a bit. Since Service Broker mechanism does not support monolog conversations (1 service and 1 queue) by default, there are simulated solutions for it:

http://www.davewentzel.com/content/service-broker-demystified-can-i-model-monologs-yes-you-can
In this article it uses 2 service and 2 queues to simulate monolog.

https://www.codeproject.com/Articles/20843/Simulating-Monolog-Conversations-with-SQL-Server
This uses 1 service - 1 queue, and also instead of conversation_handle it leverages conversation_id and at the last ends conversations as like
    DECLARE @initiatorHandle AS UNIQUEIDENTIFIER
    DECLARE @targetHandle AS UNIQUEIDENTIFIER
    
    SET @initiatorHandle = mlg.GetInitiatorHandle(@conversationID)
    SET @targetHandle = mlg.GetTargetHandle(@conversationID)
    
    IF @initiatorHandle IS NOT NULL
    BEGIN
        END CONVERSATION @initiatorHandle WITH CLEANUP
    END
    
    IF @targetHandle IS NOT NULL
    BEGIN
        END CONVERSATION @targetHandle WITH CLEANUP
    END

    RETURN
Maybe WITH CLEANUP is not necessary here. The full source code with C# demo app can be found in the link above.


I think SqlTableDependency can use one of those 2 mentioned approaches, but it might need major changes in code. Currently in SqlTableDependency, the conversation initiator is in trigger and uses "fire-and-forget" pattern which is error-prone and inreliable. See http://rusanu.com/2006/04/06/fire-and-forget-good-for-the-military-but-not-for-service-broker-conversations/

However for now, to apply a quick workaround, the following could be done:
1) Remove "end conversation"s from initiators in trigger.
2) End conversation on target part by executing "end conversation" in WaitForNotifications method (the target of conversation) after (or before) successfully notifying the message. Something like this.
3) End initiator conversation in WaitForNotifications method. Pseudo-code:
select @conversation_id = conversation_id 
from sys.conversation_endpoints with (nolock) 
where conversation_id = @target_conv_handle;
-- we got the @target_conv_handle like in the linked diff-image above

select @initiator_conv_handle = conversation_handle
from sys.conversation_endpoints with (nolock)
where conversation_id = @conversation_id and is_initiator = 1;

-- target must end conversation first
end conversation @target_conv_handle;

end conversation @initiator_conv_handle;
This logic might go into existing queue activation stored procedure as well.

Those are just thoughts. Need your feedbacks. Thanks.

BTW, dmax3348 what did you mean by "This is ok ,except one thing: this locks the sys.conversation_endpoints table; " in your comment above? How lock happens in that case?