none
How to "drainstop" a service broker queue

    Question

  • Is there a way to "drainstop" a service broker queue (i.e., allow messages currently in the queue to be processed, but not allow any new messages to be put in the queue)?

    We have an OLTP applicaiton that rebuilds the queues as part of the upgrade, but it won't do that if there are any messages because it would result in missing data. 

    Any ideas on how to drainstop it?

    Thursday, February 27, 2014 7:23 PM

Answers

  • Try inducing a routing failure.  Routing failures are temporary, and cause messages to stick in the transmission queue.

    eg

    DROP ROUTE [AutoCreatedLocal]

    To stop all message delivery. Messages will SEND successfully, but won't be delivered to the target service's queue, and will linger in the transmission queue with

    No route matches the destination service name for this conversation. Create a route to the destination service name for messages in this conversation to be delivered.

    and then:

    CREATE ROUTE [AutoCreatedLocal]  WITH  ADDRESS  = N'LOCAL' 

    To restart delivery.  If you need more granularity you can remove the AutoCreatedLocal route and create separate routes for individual services.

    David


    David http://blogs.msdn.com/b/dbrowne/



    Tuesday, March 04, 2014 4:30 PM

All replies

  • Hello,

    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.
    Thank you for your understanding and support.

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    Monday, March 03, 2014 8:24 AM
  • Is there a way to "drainstop" a service broker queue (i.e., allow messages currently in the queue to be processed, but not allow any new messages to be put in the queue)?

    You could create a configuration table with the queue status and check the queue status prior to sending a message to the service associated with the queue.  If the queue status is "drainstop", return an error instead of sending the messages.  This is very easy to do if you use a stored procedure to send messages to the target service,

     


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Monday, March 03, 2014 1:01 PM
  • Thanks for the reply, Dan, but I'm not sure I understand your methodology... it seems I would first need to update this configuration table with a queue status of drainstop... I'm assuming you're saying to do this by checking to see if there are messages present and if so set the queue status to drainstop.  But then what?  How do we prevent new messages from getting in to the queue while existing messages are still processed?
    Monday, March 03, 2014 2:47 PM
  • What I mean is that you could update the configuration table to indicate "drainstop" at the start of your upgrade process regardless existing messages in the queue(s).  The initiator service would check that table and reject/suspend any incoming messages due to the status.  The target service would continue to processes messages already in the queue until all messages are processed.  You can proceed with the upgrade once all queues are empty.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Tuesday, March 04, 2014 1:05 PM
  • Hey, Dan.  I realize now you're suggesting I rework the logic of how our service broker queues are used by adding that configuration table.  Unfortunately, I'm just the lowly Support DBA and our Developer architects and DBAs would be the ones to approve and do this should they so choose (and this would be a very time-consuming and bureacracy-laden process, so its kinda out of the question!).  But I think I see how that logic would work.

    I do, however, have another workaround in mind:

    If I were to create a script that would check each queue that was to be deleted/recreated I could have it check the number of messages in each queue, and if there were none disable the queue.  When a queue is disabled no messages in it can be processed, but no new ones can be delievered.  Because the queue was already empty the fact that no more messages can be processed would be a moot point, and because the queue is now disabled no new messages will be delievered (they'll just stay stuck in sys.transmission_queue until it comes back online) which now makes it safe to be deleted.  I'll do this for each queue, disabling them as I go when no messages are present, and when I'm done I'll check for any queues that are not disabled.  If there are I'll do a WAITFOR DELAY of 10 seconds or so and then loop through again and check the remaining, enabled queues until I finally catch them with no messages, disable them, and then continue the process until all queues are empty and disabled.  My only concern with this methodology is if there is a queue that is so busy I'll never find it empty, or it'll take too long to catch it when it is empty... still, this is the best I can think of.

    Any thoughts or ideas for improvements?

    Tuesday, March 04, 2014 3:29 PM
  • Try inducing a routing failure.  Routing failures are temporary, and cause messages to stick in the transmission queue.

    eg

    DROP ROUTE [AutoCreatedLocal]

    To stop all message delivery. Messages will SEND successfully, but won't be delivered to the target service's queue, and will linger in the transmission queue with

    No route matches the destination service name for this conversation. Create a route to the destination service name for messages in this conversation to be delivered.

    and then:

    CREATE ROUTE [AutoCreatedLocal]  WITH  ADDRESS  = N'LOCAL' 

    To restart delivery.  If you need more granularity you can remove the AutoCreatedLocal route and create separate routes for individual services.

    David


    David http://blogs.msdn.com/b/dbrowne/



    Tuesday, March 04, 2014 4:30 PM
  • Hi,

    The service broker itself does not has a "drainstop" function. I think your workaround is OK with David's suggestion.

    Friday, March 07, 2014 2:53 AM
  • While, in theory, this would probably work magnificently for most applications, it does not appear to for ours.  The reason being is in addition to deleting the queues it also deletes the services that are associated with the queues.  In my testing I've learned that if any messages in sys.transmission_queue that are bound for a queue that has its service deleted SQL treats those messages as orphaned data and purges them from sys.transmission_queue.  Now our scripts do recreate the exact same services but when they do they have a new, unique identifier value associated with them that is obviously different than the one that belonged to the service just deleted.  Because of this SQL sees it as a completely separate service and any of the new messages created during this process will be associated with the old, deleted service.  SQL sees this as orphaned data and drops it before it gets to sys.transmission_queue or drops it if they're already in sys.transmission_queue.  This means ANY new service broker traffic generated in between the time we stop service broker traffic (either by dropping the local route or disabling queues) and run our application scripts to delete/recreate the services would be lost... without the unique identifiers matching up, as far as SQL is concerned those queue's services are gone, therefore any messages bound for those queues needs to be purged.  Unfortunately, because of how we treat service broker with our application scripts I can't use this solution either.


    So, the question is... how do we drainstop the queues and then once all queues are empty allow our application scripts to delete and recreate the queues and services (and contracts and stored procs used by the queues, too, FYI) without losing any data?  I'm not sure how this can be done outside of disabling any and all of our application processes which utilize service broker so we can be sure that no new traffic is generated.  Then we just let service broker clear out any pending messages and once that's done run our scripts.  It's either that or we seriously reconsider the need to drop and recreate all the service broker queues, services, contracts, procs, etc.

    In the end it seems like this is going to be a serious mess Dev is going to have to clean up.  But if you have any other suggestions I'm open to hearing them!  Thanks for far for your help.


    Tuesday, March 11, 2014 2:45 PM
  • >seriously reconsider the need to drop and recreate all the service broker queues, services, contracts, procs, etc.

    That.

    And this might be a good time to move to a more streamlined and automated schema deployment methodology, with a tool like SQL Server Data Tools.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, March 11, 2014 2:52 PM