Service broker queues are disabling themselves, can't unearth the reason
In sys.transmission_queue the only information I'm given is "One or more messages could not be delivered to the local service targeted by this dialog."
If I re-enable one of the problem queues (there are five) and leave Activation set to OFF, the queue fills up with its messages. If I then run the Activation SP, I process the messages correctly and without issue. But as soon as I turn activation ON, if there are ANY messages in the queue, the queues will disable themselves again.
I'm completely lost. Does anyone know how I can troubleshoot this?
Answer
The queues disable themselves as a reaction to poison message handling protection. It means that your activated procedure is rolling back, probably due to some exception. Activated procedure do not have a session to send errors to, so they send it to ERRORLOG instead. Check your errorlog, should be riddled with error messages from your activated procedures.
The easiest way to troubleshoot is to simply running the activated procedure manually, from SSMS, when the activation is OFF. Try to recreate the same execution context as activation, see Internal Activation Context. The critical part is the EXECUTE AS context which is changing a lot of behavior, specially security. So try this (assumming your queue executes under dbo):
use <dbname>;
go
execute as user = 'dbo';
go
exec <sp_my_activated_proc>;
go
revert;
go
You will likely get an error message whihc is probably the cause of your repeated disables.