Service Broker Error - The conversation handle is not found

Question

A new third party application using Service Broker on SQL Server 2017 is causing the error log to report numerous failed query delivery notifications due to conversation handle not found.

I've ensured the Service Broker is enabled from sys.service_queues and have restarted the Service Broker successfully, however the errors continue.

These errors continue every two hours, making me wonder if perhaps it's an App process? I've read another option is to set the database to Trustworthy, which I haven't tried at this point.

The required components of Service Broker are present, which I've checked via system tables such as Message Types, Service Contracts, Service Queues, Services, and Endpoints. All queues have a status of 'Not Available' (by querying sys.dm_broker_queue_monitors, for example).

No messages exist in the sys.transmission_queue system table.

Querying the sys.service_queues system table and all rows have:

is_activation_enabled = 0, 
is_receive_enabled = 1, 
is_enqueue_enabled = 1, 
is_poison_message_handling_enabled = 1

Could this be due to poison messages?

I know the basics when it comes to Service Broker, but this one has me stumped.

Here are a few questions I have:

1. Any other system tables or queries I should use to further diagnose this issue?

2. It seems the messages are stuck and can't be delivered. Any ideas why this is occurring or what is causing this error?

3. Any possible solutions to resolve these errors?

SQL Server Logs:

Answer 1

You might want to try Microsoft's built-in ssbdiagnose utility.

The ssbdiagnose utility reports issues in Service Broker conversations or the configuration of Service Broker services. Configuration checks can be made for either two services or a single service. Issues are reported either in the command prompt window as human-readable text, or as formatted XML that can be redirected to a file or another program.

And possibly specifically this part:

To correctly analyze the configuration used by a conversation, run a ssbdiagnose configuration report that uses the same options that are used by the conversation. If you specify a lower level of options for ssbdiagnose than are used by the conversation, ssbdiagnose might not report conditions that are required by the conversation. If you specify a higher level of options for ssbdiagnose, it might report items that are not required by the conversation. For example, a conversation between two services in the same database can be run with ENCPRYPTION OFF. If you run ssbdiagnose to validate the configuration between the two services, but use the default ENCRYPTION ON setting, ssbdiagnose reports that the database is missing a master key. A master key is not required for the conversation.

Alternatively you might want to consider contacting Microsoft and opening a case with them.

^{Reference: ssbdiagnose Utility (Service Broker) (Microsoft | SQL Docs)}