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.
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:
Any other system tables or queries I should use to further diagnose this issue?
It seems the messages are stuck and can't be delivered. Any ideas why this is occurring or what is causing this error?
Any possible solutions to resolve these errors?