none
Records stuck in Queues

    Question

  • We are developing an application that will use Service Broker for message delivery. Everything seems to be working correctly, but during development we experienced multiple instances where messages got stuck in either the transmission or receive queues. What could be causing this? If they continue to get stuck in the transmission queue during production, do you have any suggestions for preventing and/or clearing the stuck records in this queue? I added the following code to reprocess the transmission queue but have not gotten any stuck records during testing to verify that the code works.

     

              

    SELECT@int_TransQ_Count=COUNT(*)

    FROM    sys.transmission_queue


    IF@int_TransQ_Count>0


    BEGIN;          

    DELETE@t_receive;


    INSERT@t_receive(

            queuing_order

            ,message_body

            ,conv_handle

            )

    SELECT message_sequence_number

          ,message_body

               ,conversation_handle                  

    FROM    sys.transmission_queue;

                       

    SELECT  TOP1 @nstr_conversation_handle=CAST([conversation_handle]asnvarchar(128))                  

    FROM    [sys].[transmission_queue];

                       

    SELECT@nstr_SQLStatement=N'END CONVERSATION '''+@nstr_conversation_handle+                        

    ''' with cleanup;';          

    EXECSPCTR_ADMIN.dbo.sp_executesql@nstr_SQLStatement;

                      

    GOTOREPROCESS;                        

    END;

    Tuesday, March 25, 2014 7:23 PM

All replies