none
Target sys.conversation_endpoints not purged of closed conversations RRS feed

  • Question

  • I hope someone can help me with this as we plan on using Service Broker in a high volume production environment.  The script that builds everything is available if it's needed to diagnose the problem I'm having.

    I'm having an issue where sys.conversation_endpoints on the target side of a conversation is never getting purged of closed conversations even after the 30 minute delay.  The view is filled with closed conversations and database size is growing every day.  I'm aware I can end conversation with cleanup on these conversations, but I would prefer that Service Broker behaves as expected.  I'm also aware of the problems with the fire and forget model, but my model is request/response/end between 2 databases on the same server instance.  Here's the typical series of events:

    Initiator sends request
    Target receives request
    Target processes request
    Target sends response
    Initiator receives response
    Initiator processes response
    Initiator ends conversation
    Target receives EndDialog message
    Target ends conversation

    Occasionally during the target's processing of a request, an exception is caught and the Target ends the conversation with an error:

    Initiator sends request
    Target receives request
    Target processes request and recognizes error
    Target ends conversation with error
    Initiator receives EndDialog message
    Initiator ends conversation

    Here's the trace where Database ID 23 is initiator and 24 is target, no error:

    EventClass DatabaseID TextData EventSubClass
    Broker:Conversation Group 23 1 - Create
    Broker:Conversation 23 STARTED_OUTBOUND 11 - BEGIN DIALOG
    Broker:Conversation 23 CONVERSING 1 - SEND Message
    Broker:Message Classify 23   1 - Local
    Broker:Conversation Group 24 1 - Create
    Broker:Conversation 24 STARTED_INBOUND 12 - Dialog Created
    Broker:Conversation 24 CONVERSING 6 - Received Sequenced Message
    Broker:Activation 24 1 - Start
    Broker:Conversation 24 CONVERSING 1 - SEND Message
    Broker:Message Classify 24   1 - Local
    Broker:Conversation 23 CONVERSING 6 - Received Sequenced Message
    Broker:Activation 23 1 - Start
    Broker:Conversation 23 DISCONNECTED_OUTBOUND 2 - END CONVERSATION
    Broker:Conversation Group 23 2 - Drop
    Broker:Message Classify 23   1 - Local
    Broker:Conversation 24 DISCONNECTED_INBOUND 7 - Received END CONVERSATION
    Broker:Conversation 23 CLOSED 10 - Received END CONVERSATION Ack
    Broker:Conversation 24 CLOSED 2 - END CONVERSATION
    Broker:Conversation Group 24 2 - Drop
    Broker:Activation 23 2 - Ended
    Broker:Activation 24 2 - Ended

    Here are the typical records in the target sys.conversation_endpoints.  These records never disappear:

    Normal With Error
    conversation_handle 3FE27EE5-1E86-DB11-B009-000BDB714730 53E17EE5-1E86-DB11-B009-000BDB714730
    conversation_id 0A432392-55F5-461B-87D5-0058795BC3AE BCCDFA85-86A3-43B8-9648-24FFE5C0ED3F
    is_initiator 0 0
    service_contract_id 0 0
    conversation_group_id 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000
    service_id 0 0
    lifetime 2074-12-25 21:29:28.640 2074-12-25 21:29:28.000
    state CD CD
    state_desc CLOSED CLOSED
    far_service http://my.domain.com/schemas/test/Initiator/2006-12-07 http://my.domain.com/schemas/test/Initiator/2006-12-07
    far_broker_instance 227D0898-0399-40E0-954B-C8B685EE415A 227D0898-0399-40E0-954B-C8B685EE415A
    principal_id 5 5
    far_principal_id 6 6
    outbound_session_key_identifier DEBEB4DB-D186-410B-9555-A34F8F5C9FE2 B82BB074-5AE5-4164-9D0B-53E364B0B52B
    inbound_session_key_identifier 1DBAE307-5DFF-4050-9D94-71003D8BD058 57B5C7D8-9E8B-4614-9325-5AA30AED3670
    security_timestamp 2006-12-07 18:45:52.763 1900-01-01 00:00:00.000
    dialog_timer 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
    send_sequence 1 1
    last_send_tran_id 0x550800000000 0x700700000000
    end_dialog_sequence -1 1
    receive_sequence 2 1
    receive_sequence_frag 0 0
    system_sequence 0 0
    first_out_of_order_sequence -1 -1
    last_out_of_order_sequence 0 0
    last_out_of_order_frag 0 0
    is_system 0 0

     

    Thursday, December 7, 2006 7:01 PM

Answers

  • Are you saying the conversation endpoint on the left, the 'Normal', does not get deleted after 30 min?

    The behavior you describe happens in conversations between two databases in the same instance, you are not doing anything wrong. Conversation endpoints in state 'CLOSED' that have the security_timestamp = '1900-01-01 00:00:00.000' will not be deleted. My recommendation is to create a periodic SQL Agent job that scans the sys.conversation_endpoints and ends with cleanup these conversations.

    HTH,
    ~ Remus

    Thursday, December 7, 2006 10:31 PM
    Moderator

All replies

  • Are you saying the conversation endpoint on the left, the 'Normal', does not get deleted after 30 min?

    The behavior you describe happens in conversations between two databases in the same instance, you are not doing anything wrong. Conversation endpoints in state 'CLOSED' that have the security_timestamp = '1900-01-01 00:00:00.000' will not be deleted. My recommendation is to create a periodic SQL Agent job that scans the sys.conversation_endpoints and ends with cleanup these conversations.

    HTH,
    ~ Remus

    Thursday, December 7, 2006 10:31 PM
    Moderator
  • Just thought you may want to know that you are not the only one seeing this issue, we have been battling this very same issue since august, it is good to know that I am not going crazy.  We have ellected to use a job to clean these up as well.  Please post if you hear of a resolution to the issue.
     
    Wednesday, December 20, 2006 5:35 AM
  • I too have had this same issue.  Took some time to convince people we weren't doing a fire and forget, but hopefully a solution will be pending.  Is it really the best idea to run a end conversation with cleanup to remove these?  We decided to try using a lifetime of a day duration.  I guess if you are sharing conversations that would be problematic, and if we ever had an outage on our target system we would be in a huge mess, too.

    Again, can't wait for the system to clean up after itself.

    Thursday, December 21, 2006 2:32 PM
  • I'm having this same problem.  sys.conversation_endpoints displays conversations in the CLOSED state and they never get removed from the table.  However, in my case the security_timestamp field has a real value.  The dialog_timer field is set to '1900-01-01 00:00:00.000'

    I have two service broker services setup on the same database server, on the same instance, and on the same catalog.  The initiator sends to the target and the conversation handle is cached.  Then at a later point the initiator may end the conversation.  The target receives the EndDialog message type and ends it's conversation.  The initiator's conversation handle does not appear in sys.conversation_endpoints, only the target's conversation handle is displayed.

    How could this be correct behavior?

    Thanks,
    Kevin Berridge

    Thursday, December 13, 2007 3:27 PM
  • Is the datetime in security_timestamp field in the future or in the past? Note that the time is UTC time. 
    The correct behavior is for the target endpoint to be deleted after the security_timestamp has expired, which is usually in 30 minutes after the first reply from target to the initiator was acknowledged. In the cases when the target never sends any reply to the initiator (including an EndDialog reply) then it is known that the target will be leaked, but this is an incorrect message exchange patter (fire-and-forget) not only because of this problem but for other reasons too, see Fire and Forget: Good for the military, but not for Service Broker conversations
    However in the same instance and same database even the fire-and-forget scenario should work and not leave the target endpoint leaked. If you have a consistent repro scenario for a case when the target endpoint security_timestamp is initialized but the endpoint is not deleted after this timestamp has expired I suggest you contact the product team, perhaps using Connect: SQL Server 
    Thursday, December 13, 2007 4:00 PM
    Moderator
  • Tuesday, June 3, 2008 4:54 PM
  • Initiator sends request
    Target receives request
    Target processes request and recognizes error
    Target ends conversation with error
    Initiator receives EndDialog message
    Initiator ends conversation

    Is it possible that by having the INITIATOR ending the conversation first, as opposed to the TARGET ending the conversation first, that this causes the sys.conversation_endpoints table to *not* purge after 30 minutes?  Not purging because the dialog_timer field is not being set when the INITIATOR sends the end conversation rather than the target?

    Conversation endpoints in state 'CLOSED' that have the security_timestamp = '1900-01-01 00:00:00.000' will not be deleted.

    This is incorrect.  I have numerous examples of where the security_timestamp is 10+ days in the past, but the record did not delete itself. 
    However, the dialog_timer field in all records = '1900-01-01 00:00:00.000'

    Did Remus simply pick the wrong field?
    Wednesday, November 11, 2009 8:47 PM
  • Remus, should I end conversations with cleanup when I have endpoints that are conversing?
    Tuesday, February 9, 2010 4:33 PM