locked
endpoints RRS feed

  • Question

  • Hi All ,

     We have a database where there is no table in it  (no data Storage)  we have enables Service brokers and queues for application purpose. 

    now i can see the mdf file is  keep growing.

     found that this query returning 

    select count(1) from sys.conversation_endpoints with(nolock)

    Millions of records. 

     if so how clear this . this is an OLTP (24/7) environment . is there as way to clear without down time .

    Regards

    K.muthus 


    k.muthus


    • Edited by kmuthus Wednesday, September 23, 2015 2:26 PM
    Wednesday, September 23, 2015 1:54 PM

Answers

  • You can clear all conversations by running the following:

    ALTER DATABASE <database> SET NEW_BROKER WITH ROLLBACK IMMEDIATE.

    HTH

    Wednesday, September 23, 2015 2:02 PM
  • Specifing the NEW_BROKER does not actually remove the service broker from the database. it closes all currently opened conversations in one statement. 

    You could also close all conversations manually one at a time through a loop, but if you have millions of conversations that would take some time...

    From MSDN:

    NEW_BROKER

    Specifies that the database should receive a new broker identifier. Because the database is considered to be a new service broker, all existing conversations in the database are immediately removed without producing end dialog messages. Any route that references the old Service Broker identifier must be re-created with the new identifier.

    Wednesday, September 23, 2015 3:12 PM

All replies

  • You can clear all conversations by running the following:

    ALTER DATABASE <database> SET NEW_BROKER WITH ROLLBACK IMMEDIATE.

    HTH

    Wednesday, September 23, 2015 2:02 PM
  • Thanks we need to make sure that Service borker need tobe there .   

    k.muthus

    Wednesday, September 23, 2015 2:46 PM
  • Specifing the NEW_BROKER does not actually remove the service broker from the database. it closes all currently opened conversations in one statement. 

    You could also close all conversations manually one at a time through a loop, but if you have millions of conversations that would take some time...

    From MSDN:

    NEW_BROKER

    Specifies that the database should receive a new broker identifier. Because the database is considered to be a new service broker, all existing conversations in the database are immediately removed without producing end dialog messages. Any route that references the old Service Broker identifier must be re-created with the new identifier.

    Wednesday, September 23, 2015 3:12 PM