none
End Conversations not clearing out

    Frage

  • Hi There

    I am running a service broker application on a slow WAN network country wide.

    I have noticed that when I look at sys.conversation_endpoints on the central instance of sql server that sends messages to all the other instances, i have many conversation where the status is DISSCONNECT_INBOUND or the far_service is null. These are essentially stale conversation.

    I have a central sql server that initiates a conversation with other instances of sql server, when the other servers send a message back they then do an end conversation, both the central server and other instances of sql server have the logic on the activated stores proc to end conversation as below.

    IF @msgType = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
    BEGIN
    WAITFOR DELAY '00:00:01'
    PRINT 'Admin Activated SP End Conversation'
    END CONVERSATION @ConvHandle
    END

    However it seems that when a message is sent and the remote instance ends the conversation the conversation status immediately goes to disconnect_inbound and never goes away on the initiator (central).

    I cannot replicate this on my test environment as it is on a fast LAN, all conversations end and clear 100% on a LAN test environment with the same Service Broker code, so I am thinking it has something to do with the central instance (initiator) not receiving the end conversation immediately.

    Can anyone assist, as I am constantly left with thousands of stale conversation that do not seem to clear and end correctly on the central (initiator) side.

    Regards

    Mittwoch, 23. Januar 2013 07:08

Alle Antworten

  • Thanks for this, but I am not looking for a way to clean up conversations, I know how to do that.

    I do not understand why they do not clear automatically as they should because I end the conversation on both sides.

    It works 100% on a fast network but not on a slow network, so I am wondering if there is some sort of  end conversation timeout setting I am unaware of. 

    If the end conversation message is not received within a certain time period is that what causes me to see this behavior? How do I remedy it?

    Freitag, 1. Februar 2013 07:45
  • Hi,

    since you will have the conversation handles of the conversations that are not ending, could you please run the SSBDiagnose utility on those conversations ?

    This should give us more troubleshooting information.



    Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

    Samstag, 2. Februar 2013 16:29
  • We could use a bit more information but lets make sone guesses.

    If the conversations are not ending, then it looks like this code is not executing.

    IF @msgType = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
    BEGIN
    WAITFOR DELAY '00:00:01'
    PRINT 'Admin Activated SP End Conversation'
    END CONVERSATION @ConvHandle
    END

    Have you tried executing  END CONVERSATION @ConvHandle  in SSMS on just one of these 'dead' conversations to see if it clears.
    Try using Profiler to see if the code executes, or change the Print to an Insert into a log table.

    Why doen't it execute?
    Well, if it works on the test environment, but not on the WAN, the code must be right! so what is different.

    Guess 1:
    The activation procedure containing this code is not enabled
    (Too obvious right!)

    Guess 2:
    Maybe the return Routes are wrong on the WAN remote servers so the end dialogs are not getting through.
    This is the most common fault I see following a new deployment.

    Guess 3:
    Security! it is always security in my experience. Security is the bane of my life
    Check with SQL Profiler. Turn on the Broker events and the Security Audit events for Service Broker and disable all the SP/TSQL stuff
    Maybe the End dialogs are being dropped because of security.

    It could be either end so run the profiler on both servers.

    Good luck - hope this helps

    Sonntag, 10. Februar 2013 09:37