How to clean sys.conversation_endpoints

Question

I have a table, a trigger on the table implemented using service broker. More than Half million records are inserted daily into the table.

The asynchronous SP is used to check sveral condition by using inserted data and update other tables. It was running fine for last 1 month and the SP was get executed withing 2-3 seconds of insertion of record. But now it take more than 90 minute.

At present sys.conversation_endpoints have too much records.

(Note that all the table are truncated daily as I do not need those records day after)

Other database activities are normal (average 60% CPU Utilization).

Now where i need to look??

I can re-create database without any problem but i don't think it is a good way to resolve the problem

Answer 1

you can 'end conversation @handle' or 'end conversation @handle with cleanup'