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

sql sql-server-2008 service-broker
makitocode picture makitocode · Sep 11, 2014 · Viewed 24.4k times · Source

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

Remus Rusanu picture Remus Rusanu · Sep 11, 2014

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.

Related questions

How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More
Rename column SQL Server 2008

I am using SQL Server 2008 and Navicat. I need to rename a column in a table using SQL. ALTER TABLE table_name RENAME COLUMN old_name to new_name; This statement doesn't work.

Read More