End 35 Million conversations quickly - production issue!

We have a system that has 35 million conversations piled up. We didn't know to explicitly end the conversation once the processing has completed. Oops. Now, our production box has 35 mm sitting in the table, and we have run into the problem where the amount in sys.conversation_endpoints has exceeded memory and they are being dumped into tempdb, which is killing our disk space, thus bringing the box down. We have fixed the code to end the conversations, but we now have to end the conversations in a hurry. If we select one by one out of the table and end the conversation via END CONVERSATION, it is slow. Very slow. It will finish in a few months. :(

Does anyone know how to get rid of these conversations in a hurry? All of the messages have been applied to our system, so killing the conversations will (should) have no affect on the processed data. Something like a TRUNCATE statement?

Thank you so much in advance,

John Hennesey

The best way is to END CONVERSATION in batches and let it run. Make sure you batch 100-200 ENDs in one transaction batch for better performance.

ALTER DATABSE ... SET NEW_BROKER has the risk of distrbing existing, active conversations. Also, in RTM and SP1 is just as slow as a batch of END CONVERSATION ... WITH CLEANUP, since that what it does internally.

If none of these work for you, then if you dare installing SP2 CTP, the internals of ALTER DATABASE ... SET NEW_BROKER have changed to truncate and will do what you need, fast. You will have to suspend the production system for a short while and make sure there are no active dialogs, to prevent active dialogs from being affected. also, if your routes use the broker_instance, you will have to update them after the NEW_BROKER operation.

HTH,
~ Remus

|||

Remus -

Thank you very much for your response. We did exactly what you said and it worked. It was just a bit slower than what we needed. It's good to know the CTP will have another option for handling this - unfortunately since this is a production system CTP isn't an option. We split it up by queue name and ran 10 queries in parallel to end the conversations a little quicker. It took about 24 hours, and with the code changes we are in good shape.

Thank you for your response,

John