locked
Service Broker Filling Tempdb RRS feed

  • Question

  • I have SB setup as a fire and forget setup. I have identified that SB is the cause of my TEMPDB growing out of control. It allocates many pages but never deallocates any. The initiator ends the conversation straight away, while the target ends with cleanup, I have no conversations hanging around in the sys.conversations_endpoints table but I never see the conversation move to the CD status they always remain in the DI DISCONNECTED_INBOUND status.

    Question 1 is this the cause of TEMPDB filling up.

    Question 2 should the allocated pages in TEMPDB clear out when with cleanup is executed.

    I know both are bad but that is the way it is setup.

    Monday, December 3, 2012 6:09 PM

Answers

  • The solution is written in a  way that the initiator kicks ends the conversation first.

    The target then processes and ends the conversation with clean up

    Should this clear up all the dialogs in the TEMPDB. As this is not currently happening. I can see that the sys.conversation_endpoints is clearing down but not TEMPDB.

    • Marked as answer by Iric Wen Sunday, December 9, 2012 9:24 AM
    Thursday, December 6, 2012 1:36 PM
  • Do you have an automatic activation proc on your initiator queue that kicks off an END conversation ? If yes - thats good and it will end the conversation on initiator side when the target side sends and END DIALOG. For the target side to END dialog, you should have and END conversation in the same proc that has your RECEIVE.

    BOL is correct in recommending that your stable code base should NEVER end a conversation with CLEANUP. My recommendation for using END CONVERSATION WITH CLEANUP, is for adhoc use when you see conersations pilling up with "DI" status and you need to change then to "CD" status on a ad-hoc basis. It should not be a part of your stable code base.


    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

    • Marked as answer by Iric Wen Sunday, December 9, 2012 9:24 AM
    Thursday, December 6, 2012 4:26 PM

All replies

  • Please refer to this thread with a same/similar problem. http://social.msdn.microsoft.com/Forums/en-AU/sqlservicebroker/thread/b0a25c4d-0f21-4bd5-a253-33b6eaaef05a


    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

    Monday, December 3, 2012 11:08 PM
  • The link inside of the link doesn't work.
    Tuesday, December 4, 2012 8:29 AM
  • The the content of that link is an article explaining the fact that you have to close service broker dialog. 

    Your temporary solution would be to fire off a command - END CONVERSATION <conversation_handle> WITH CLEANUP ; for each open conversation that is in DI status.

    programmatic solution in your code to prevent conversation from remaining open, is to close them in your proc that does the RECEIVE. 


    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

    Tuesday, December 4, 2012 11:23 PM
  • The solution is written in a  way that the initiator kicks ends the conversation first.

    The target then processes and ends the conversation with clean up

    Should this clear up all the dialogs in the TEMPDB. As this is not currently happening. I can see that the sys.conversation_endpoints is clearing down but not TEMPDB.

    • Marked as answer by Iric Wen Sunday, December 9, 2012 9:24 AM
    Thursday, December 6, 2012 1:36 PM
  • Does the Target need verification the the initiator before the dialogs will end properly. Or should with cleanup take care of this. BOL advises that WITH CLEANUP should not be used in code.
    Thursday, December 6, 2012 1:38 PM
  • Do you have an automatic activation proc on your initiator queue that kicks off an END conversation ? If yes - thats good and it will end the conversation on initiator side when the target side sends and END DIALOG. For the target side to END dialog, you should have and END conversation in the same proc that has your RECEIVE.

    BOL is correct in recommending that your stable code base should NEVER end a conversation with CLEANUP. My recommendation for using END CONVERSATION WITH CLEANUP, is for adhoc use when you see conersations pilling up with "DI" status and you need to change then to "CD" status on a ad-hoc basis. It should not be a part of your stable code base.


    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

    • Marked as answer by Iric Wen Sunday, December 9, 2012 9:24 AM
    Thursday, December 6, 2012 4:26 PM
  • Hello, I had a similar issue.
    Millions of conversations were not cleaned up properly , and ended up using over 10GB in tempdb internal objects.

    Having cleaned them up with END CONVERSATION WITH CLEANUP, the conversations are gone from sys.conversation_endpoints, but the space in tempdb has not been released.

    I was hoping some garbage clean up would kick in, but the space is still allocated to internal tasks 8 hours later.

    BRKR TASK       
    BRKR EVENT HNDLR

    Any tips on having this space released?

    thanks.

    Monday, December 17, 2012 7:49 PM
  • KB3005011 also mentions similar symptoms when using the "fire-and-forget" pattern.
    Thursday, December 11, 2014 5:57 PM