Home Forums Programming Service Broker I want to be able to stop my queue when there is a backlog... RE: I want to be able to stop my queue when there is a backlog...

  • What I usually do in this sort of situation is simply to disable the queue (alter queue dbo.[queuename] with status = off). The call to receive will fail on any and all instances already running and any locks are freed to do my maintenance task. Also by disabling the queue I automatically informed Service Broker not to launch any new instances for as long as the queue is disabled. Then, when I'm done or at any time I do want the process to resume I issue another alter queue command to set the queue status back to on and service broker will automatically start launching new instances of the configured procedure.

    For this specific purpose I always test for error code 9617 after calling waitfor (receive) in all service broker procedures: all other error codes are reported and logged as errors, only this error code 9617 makes the procedure exit silently.

    Actually error 9617 is the same error code that occurs when the queue gets disabled after 5 rollbacks due to a poison message. I don't need this condition reported repeatedly in my logs every time a process is retried, so I ignore this error code and instead set up a monitoring service for disabled queues. This way I am warned exactly once when a poison message does disable any of my queues and my logging system does not get flooded by the error.



    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]


    If you don't have time to do it right, when will you have time to do it over?