Dave Satz on Fri, 08 Nov 2013 22:41:35
ALTER QUEUE Qname WITH ACTIVATION (STATUS = 'OFF');
If we use ALTER QUEUE to de-activated a queue, what happens to the currently executing instances of the activation SProc. For example, will the activation SProc immediately stop executing and roll the message back onto the queue or will the activation keep running until all the messages in the queue are processed?
Allen Li - MSFT on Mon, 11 Nov 2013 02:27:14
From Books Online for SQL Server 2012, we got the following information:
When a queue with a specified activation stored procedure contains messages, changing the activation status from OFF to ON immediately activates the activation stored procedure. Altering the activation status from ON to OFF stops the broker from activating instances of the stored procedure, but does not stop instances of the stored procedure that are currently running.
So Altering the activation status from ON to OFF won’t roll back the message in the queue. For more detail information, you can refer to the following link:
ALTER QUEUE (Transact-SQL)
TechNet Community Support
Dave Satz on Mon, 11 Nov 2013 13:27:36
Thanks - I should I read the remarks section closer!