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