Well I'm sorry that I got everyone mad at me. What do I need to do to get out of the Dog House?

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() )

It does not post very well.

Partial Results:

ExternalMailServicedboExternalMailQueueINACTIVE

InternalMailServicedboInternalMailQueueINACTIVE

http://schemas.microsoft.com/SQL/Notifications/EventNotificationServicedboEventNotificationErrorsQueueNot available

http://schemas.microsoft.com/SQL/Notifications/QueryNotificationServicedboQueryNotificationErrorsQueueNot available

http://schemas.microsoft.com/SQL/ServiceBroker/ServiceBrokerdboServiceBrokerQueueNot available

syspolicy_event_listenerdbosyspolicy_event_queueINACTIVE

This Server is not that new but the latest that I have received. I installed in late Feburary and created the first Database on 2012-02-29.

I will soon have 2 new Servers, I will use the same script to setup the Account & Pofile and it will be interesting to see what happens.