none
What happens when a queue is de-activated?

    Question

  • Hello:
    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?


    TIA,
    Dave
    Friday, November 08, 2013 10:41 PM

Answers

  • Hi Dave,

    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)
    http://technet.microsoft.com/en-us/library/ms189529.aspx

    Best Regards,
    Allen Li

    If you have any feedback on our support, please click here.


    Allen Li
    TechNet Community Support

    Monday, November 11, 2013 2:27 AM

All replies

  • Hi Dave,

    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)
    http://technet.microsoft.com/en-us/library/ms189529.aspx

    Best Regards,
    Allen Li

    If you have any feedback on our support, please click here.


    Allen Li
    TechNet Community Support

    Monday, November 11, 2013 2:27 AM
  • Thanks - I should I read the remarks section closer!
    Monday, November 11, 2013 1:27 PM