Asked by:

Conversations stuck in state of CONVERSING and not processing messages

Question

Hey, all. I'm having an issue where we have a number of table triggers which use service broker to document any DML statements run against those table. The changes are inserted via service broker in to some tables we use for
this auditing. When I trigger these events I can see the conversations with service broker started by querying sys.conversation_endpoints. However, they stay in a state of CONVERSING and never seem to be processed by service broker until almost a
day later. I've tried disabling/re-enabling service broker on the database (yes, this is in Test), and also purging the conversations and trying again, but it seems there is a serious delay in getting service broker to process them.

Is there a way where I can force service broker to process these messages? And what is an effective way to troubleshoot performance issues affecting service broker?

All replies

Just sending messages isn't enough to cause them to be processed. First you need to ensure that the messages are being delivered to the target queue (which may require routing to another database or broker instance), then ensure that they are processed.
The processing might use internal activation, external activation, or a polling or manual process.

You're right, David. The problem seems the messages aren't making it to their designated queue. I've already confirmed that service broker is still enabled and that the route for the target queue exists. I'm looking in to using ssbdiagnose
next but any additional advice would be greatly appreciated. Thanks!