Monitoring Queue status

Hi,

I'm new to the service broker service. All I want to do is to monitor the queue status. If the queue is disabled, send me an email alert.

Can you let me know what's the best way to accomplish it?

Thanks,

Jia

if ((select is_receive_enabled from sys.service_queues where name = 'PublisherQueue ') = 0)

begin

execute msdb.dbo.sp_send_dbmail 'MyDBMail', 'me@.abc.com', @.subject = 'queue is down'

end

|||

You could use SQL Trace Events to do this for you. Here are the steps... you can lookup Books Online for exact syntax on what statements you need to use to achieve this:

1. Create a Service that will get notified whenever your queue gets disabled. Make sure that the service implements the PostEventNotificationContract (the actual name is a big URL I haven't memorized... look at sys.service_message_types).

2. Write a service program (as a stored proc) for the above service that accepts event notification messages from the service queue, parses the required information and sends e-mail via DBMail.

3. Create an event notification for QUEUE_DISABLED_EVENT for your user queue.

When your user queue gets disabled, the event service will receive an event message. Activation will launch your service program which will send you an e-mail.

|||Thanks Rushi!