How to check if my Queue (Service Broker) is enable or not?

Question

rarely, for reasons that have not yet identified, my queue is disabled, when this happend, I enable again with this query:

  ALTER QUEUE [MyQueue] WITH STATUS = ON;

but, i want to know when the queue is disabled, such as an event (T-SQL), or check each "x" time if queue is enabled.

Answer 1

Look into sys.service_queues:

select is_receive_enabled
from sys.service_queues
where name = N'MyQueue';

Your queue gets disabled by the poison message handling mechanism. When this happens an event is fired which can be captured via Event Notification, see Service Broker Application Queue Disable Event.