Purge records of Sys.Transmission_Queue of Service Broker in SQL Server.

Today i got one issue on service broker for sys.transmission_queue.
Service broker configured well and working fine, suddenly service broker tranmission_queue goes high and high.
I have checked Transfer_Status to check the error if there is any, but i can’t see any error in Transfer_Status.

So, the question is what to do if SYS.Transmission_Queue stucks.
I had searched on web and read many bolg’s and forums but can’t find proper way to subsidize the transmission queue.

Finally, i have started to send the message manually from sys.transmission_queue and clean the conversation.

The steps that i followed is as below.

Step1 : Insert all Sys.Transmission_Queue data into temp table.

SELECT *
INTO MY_TRANSMISSION_QUEUE
FROM SYS.TRANSMISSION_QUEUE

Step2 : Now purge records of Sys.Transmission_Queue.

BEGIN
BEGIN TRY
DECLARE @Convhandle UNIQUEIDENTIFIER

DECLARE CONV CURSOR FOR

SELECT S2TRANSQUEUE.CONVERSATION_HANDLE
FROM SYS.CONVERSATION_ENDPOINTS AS S2CONVEND WITH(NOLOCK)
 INNER JOIN SYS.TRANSMISSION_QUEUE S2transqueue WITH(NOLOCK)
 ON S2TRANSQUEUE.CONVERSATION_HANDLE = S2CONVEND.CONVERSATION_HANDLE

OPEN CONV
FETCH NEXT FROM CONV INTO @Convhandle

WHILE @@FETCH_STATUS = 0
 BEGIN
 END CONVERSATION @Convhandle WITH CLEANUP
 FETCH NEXT FROM CONV INTO @Convhandle
 PRINT @Convhandle
 END

CLOSE CONV
DEALLOCATE CONV
END TRY

BEGIN CATCH
PRINT Error_message()
END CATCH

Step3 : Alter table and add one new column TransferStatus to check manually transfer status.

ALTER TABLE MY_TRANSMISSION_QUEUE
ADD TRANSFERSTATUS BIT

UPDATE MY_TRANSMISSION_QUEUE
SET TRANSFERSTATUS = 0

Step4 : Now send data manually from temp table and change the TransferStatus column.

DECLARE @Cnt INT
SELECT @Cnt = Count(*)
FROM My_transmission_queue(NOLOCK)
WHERE TRANSFERSTATUS = 0

WHILE( @Cnt > 0 )
 BEGIN
 DECLARE @Con_hand UNIQUEIDENTIFIER
 DECLARE @Xml XML
 DECLARE @Seq INT
 SELECT TOP 1 @Con_hand = CONVERSATION_HANDLE,
 @Xml = Cast(MESSAGE_BODY AS XML),
 @Seq = MESSAGE_SEQUENCE_NUMBER
 FROM My_transmission_queue(NOLOCK)
 WHERE TRANSFERSTATUS = 0
 ---Process Queue
 DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
 DECLARE @RequestMsg NVARCHAR(100);

BEGIN TRANSACTION

BEGIN DIALOG @InitDlgHandle
 FROM SERVICE [SB://Service/Audit/Initiator]
 TO SERVICE N'SB://Service/Audit/Target'
 ON CONTRACT [SB://Contract/Audit]
 WITH ENCRYPTION = ON;

 SEND ON CONVERSATION @InitDlgHandle
 MESSAGE TYPE [SB://Message/Audit/Request]
 (@Xml);

COMMIT TRANSACTION
 --End of Process Queue.

UPDATE MY_TRANSMISSION_QUEUE
 SET TRANSFERSTATUS = 1
 WHERE TRANSFERSTATUS = 0
 AND CONVERSATION_HANDLE = @Con_hand
 AND MESSAGE_SEQUENCE_NUMBER = @Seq
 SET @Cnt = @Cnt - 1
 END

Note: This script is useful if you have only one type of service. If you have more than one service in sys.transmission_queue you have to change the code as your requirement.

 
Thanks & Regards,
Nirav Gajjar