Home / server / Questions / 92818
In Process
Aaron Weiker
Asked: 2009-12-10 15:09:03 +0800 CST

How do I enable a service broker queue when it is disabled?

  • 772

I have a scenario where an external application is monitoring a queue and then taking an action when something is put into the queue. I just wrapped this to happen inside of a transaction to try to prevent things from getting out of the queue if the action has failed. What is happening is that under certain conditions the queue will get disabled if I have a failure in my application that causes the transaction to rollback.

What I'm looking for is a way to enable the queue without opening up SQL Server Management Studio.

sql-server-2008

1 Answers

  1. The poison message detection will kick in if 5 consecutive RECEIVE statements are rolled back. there is no way to disble this, nor there is any way to re-enable the queue in the queue reader. Because the disabling is asynchronous, it can disable the queue after your logic tries to re-enable a disabled queue.

    The proper solution is never to rollback intentionally and handle all errors. However, this isn't always possible. My recommendation would be to hook up an event notification on the queue for the QUEUE_DISABLED event. This will fire a notification and you can react to this notification.

    Queues can be re-enabled with:

    ALTER QUEUE [queuename] WITH STATUS = ON;
    • 3