Home Library Learn Downloads Support Community
Sign in | United States - English | |
1 out of 3 rated this helpful - Rate this topic

sys.dm_broker_queue_monitors

SQL Server 2005

Updated: 14 April 2006

Returns a row for each queue monitor in the instance. A queue monitor manages activation for a queue.

Column name Data type Description

database_id

int

Object identifier for the database that contains the queue that the monitor watches. NULLABLE.

queue_id

int

Object identifier for the queue that the monitor watches. NULLABLE.

state

nvarchar(32)

State of the monitor. NULLABLE. This is one of the following:

  • INACTIVE
  • NOTIFIED
  • RECEIVES_OCCURRING

last_empty_rowset_time

datetime

Last time that a RECEIVE from the queue returned an empty result. NULLABLE.

last_activated_time

datetime

Last time that this queue monitor activated a stored procedure. NULLABLE.

tasks_waiting

int

Number of sessions that are currently waiting within a RECEIVE statement for this queue. NULLABLE.

This number includes any session executing a receive statement, regardless of whether the queue monitor started the session. This is if you use WAITFOR together with RECEIVE. Basically, these tasks are waiting for messages to arrive on the queue.
ms177628.note(en-US,SQL.90).gifNote:

Requires VIEW SERVER STATE permission on the server.

A. Current status queue monitor

This scenario provides the current status of all message queues.

Copy 
SELECT t1.name AS [Service_Name],  t3.name AS [Schema_Name],  t2.name AS [Queue_Name],  
CASE WHEN t4.state IS NULL THEN 'Not available' 
ELSE t4.state 
END AS [Queue_State],  
CASE WHEN t4.tasks_waiting IS NULL THEN '--' 
ELSE CONVERT(VARCHAR, t4.tasks_waiting) 
END AS tasks_waiting, 
CASE WHEN t4.last_activated_time IS NULL THEN '--' 
ELSE CONVERT(varchar, t4.last_activated_time) 
END AS last_activated_time ,  
CASE WHEN t4.last_empty_rowset_time IS NULL THEN '--' 
ELSE CONVERT(varchar,t4.last_empty_rowset_time) 
END AS last_empty_rowset_time, 
( 
SELECT COUNT(*) 
FROM sys.transmission_queue t6 
WHERE (t6.from_service_name = t1.name) ) AS [Tran_Message_Count] 
FROM sys.services t1    INNER JOIN sys.service_queues t2 
ON ( t1.service_queue_id = t2.object_id )   
INNER JOIN sys.schemas t3 ON ( t2.schema_id = t3.schema_id )  
LEFT OUTER JOIN sys.dm_broker_queue_monitors t4 
ON ( t2.object_id = t4.queue_id  AND t4.database_id = DB_ID() )  
INNER JOIN sys.databases t5 ON ( t5.database_id = DB_ID() )

Release History

14 April 2006

Changed content:
  • Modified WHERE clause in example.
Did you find this helpful?
(1500 characters remaining)
Community Content Add
Annotations FAQ
Disabled service broker queue detection.

The example code above is not very useful. It fails to display queues that have been shut down with a poison-pill situation.

Here is code I use to identify queues that are not merely "inactive", but are actually not working any more:

-- Disabled queues
SELECT [name] from sys.service_queues
WHERE is_activation_enabled = 1
AND is_ms_shipped = 0
AND ((is_receive_enabled = 0) OR (is_enqueue_enabled = 0))

History