locked
SQL Service Broker queue is not activated after the server reboot RRS feed

  • Question

  • After the sql server reboot, service broker queues are not activated automatically. Even though the queues are on 'enabled' stage, we had to manually disable and enable the queues to start the processing immediately. Otherwise it takes around 3 to 4 hours to automatically activates the queue.

    After the reboot broker queue monitor is not listing our custom broker queue. (select * from sys.dm_broker_queue_monitors)
    Once we disable and enable the queue, the queue monitor is displaying the queue Ids with the current date and time as last activated time.
    If we don't perform the step 2, then the broker queues get activated automatically after 3 to 4 hours. Not sure how it works.
    following query shows the pending messages in the queue for processing after the reboot.
        select p.rows, sq.name

          from sys.objects as o

          join sys.partitions as p on p.object_id = o.object_id

          join sys.objects as q on o.parent_object_id = q.object_id

            join sys.service_queues sq on sq.object_id = q.object_id

          where p.index_id = 1 
    I have already verified following parameters; activation procedure, Execute as principle, is_activation_enabled (1), is_receive enabled (1), is_enqueue_enabled (0), is_poison_message_handling(1) I haven't seen any issues with the parameter values.

    Activation procedure has the RECEIVE value as TOP (1)

    After the server reboot dm_broker_queue_monitors are not listing our custom service broker queues immediately. Could someone please help me to identify the issue.

    Thanks.
    Saturday, November 28, 2020 10:44 PM

All replies

  • forum migrated to mentioned below link

    https://docs.microsoft.com/en-us/answers/index.html

     hope answer the question if issue resolve please accept answer
    Sunday, November 29, 2020 12:32 AM
  • Hi NPT_NPT,
    For questions about sql server, I suggest you ask the question on the Microsoft Q&A forum and you can get more professional answer.
    Thank you for your understanding.
    Best Regards,
    Daniel Zhang


    "Visual c#" forum will be migrating to a new home on Microsoft Q&A ! We invite you to post new questions in the "Developing Universal Windows apps" forum’s new home on Microsoft Q&A ! For more information, please refer to the sticky post.

    Monday, November 30, 2020 5:36 AM
  • Thanks @KHURRAM. I have already posted my question here - https://docs.microsoft.com/en-us/answers/questions/178929/sql-service-broker-queue-is-not-activated-after-th.html

    Monday, November 30, 2020 11:04 PM
  • Thanks @Daniel. I have already posted my question here - https://docs.microsoft.com/en-us/answers/questions/178929/sql-service-broker-queue-is-not-activated-after-th.html
    Monday, November 30, 2020 11:04 PM