Purge target Transmission queue of Service Broker in SQL Server

Purge target Transmission queue of Service Broker in SQL Server

Last Sunday I have got very interesting question from one of my blog reader and before we discuss his question and solve it, let me give you some basic understanding regarding how message traverse between Initiator and Target Queue in very simple word by eliminating deep technicality.

When any message comes to service broker, message has to pass through various way including validation until it reaches to target queue and get processed.

Transmission Queue plays an important role in whole service broker architecture. Transmission Queue works as a “postman” between Initiator Queue and Target Queue.

Let me give you this understanding in simple words, without including deep technical explanation.

Suppose we have two server “S1” and “S2”. We have service broker implemented between these two servers. S1 is an initiator and S2 is a target. S1 sends message to process to S2 and S2 replies back to S1 via Transmission Queue.

Here is simple explanation in brief wording.

  • S1 send message to process to S2 via Transmission Queue and starts conversation dialog.
  • S2 receive message from S1’s Transmission Queue and acknowledge the same to S1 via S2’s Transmission Queue
  • Now, S2 process the message and again acknowledge the same to S1 via S2’s Transmission Queue
  • Finally message get processed and S2 send acknowledge of the same via Transmission Queue to S1 to finally close the conversation dialog
  • When S1 closes the conversation, it acknowledge the same to S2 and message gets deleted from the transmission queue of S2.

Please note that, our task get completes in first three steps whereas fourth & fifth steps are formality to close open conversation in S1 & S2.

I have written articles to watching the message count currently stays inside different queues.

  • Find Service Broker Queue Count in SQL Server (Click Here)
  • Get Transmission queue row count in Service Broker (Click Here)

One of the blog reader has implemented the functions I have given in both of the above articles and found that his initiator and target queue always having few hundreds message in peak business hours and become even zero many times but his transaction queue in target server (S2 in our example) having millions of message unprocessed. He wanted me to help him in dealing with that huge queue.

Obviously I don’t have access to his server so I have to guess after getting certain information from him. I have asked him to execute following query in his S2 server ‘s database where he has setup service broker and transmission queue showing millions of records.

[sourcecode language=”sql”]
SELECT
‘MessageNeedsToClose’ AS ActionToTake,
COUNT(*) AS RowsCount
FROM
sys.transmission_queue
WHERE
message_body IS NULL
AND is_end_of_dialog = 1
AND enqueue_time < getdate() – 7
UNION ALL
SELECT
‘MessageToKeep’ AS ActionToTake,
COUNT(*) AS RowsCount
FROM
sys.transmission_queue
WHERE
message_body IS NOT NULL
GO
[/sourcecode]

He sent me approx million+ records in “MessageNeedsToClose” and less then hundred count comes in “MessageToKeep”.

SELECT query before the UNION ALL clause checks where the row in transmission queue is having any message inside it or not. If Message_Body is NULL then there is no message at all. If “is_end_of_dialog” is 1 then that message is just an acknowledgment nothing else. So if there is no message to process in message_body and if message is just an acknowledgment, we can probably close it to fix the high number of queue one time, if message is already closed from initiator side.

Reader has already told me that transmission queue on initiator side (in server S1) always stays below 1000 and become zero also so there is no chance that the million message we have seen in S2 is still there open in S1.

Since I didn’t have access to his server to investigate further and the messages we have in target transmission queue is not a real business process, we can close that dialog. If I would have access to server, I would run profiler for broker in S1 as well as in S2 to see what is happening with that big transmission queue but the reader was already convinced and wanted to settled down by closing the conversation of dialog.

I have written one customized Stored procedure for him to create on Server S2 in the database where he has Service Broker setup.

[sourcecode language=”sql”]
CREATE PROCEDURE [CleanTransmissionQueueByEndingDialog]
AS
SET NOCOUNT ON
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
AND S2TransQueue.message_body is null
AND S2TransQueue.is_end_of_dialog = 1
AND S2TransQueue.enqueue_time < getdate() – 7

–I HIGHLY recomment uncomment the following WHERE clause to check
–where same message is alive in initiator end or not
–if it is alive, we shouldn’t close that message.

–WHERE NOT EXISTS(
— SELECT 1
— –S1 is a Linked Server, you have to replace it with your linked server of Initiator
— –"DatabaseName" should be replace by your database name where you have setup
— –Service Broker in Initiator Server.
— FROM S1.DatabaseName.sys.conversation_endpoints S1ConvEnd with(nolock)
— WHERE S1ConvEnd.conversation_id = S2ConvEnd.conversation_id
–)

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
END
GO
[/sourcecode]

Please note that I have investigated this issue without accessing live server of blog reader. Whatever suggestions I have given here is going to affect your live production environment if you are executing this script in production environment so please give a second thought and do it at your own risk.

I have written some other articles related to service broker, if you are directly landed to this article and new to Service broker, have a look at following articles which covers Service Broker concept from scratch.

  • Importance of Service Broker in SQL Server (Click Here)
  • Introduction of Service Broker in SQL Server (Click Here)
  • Enable Service Broker in SQL Server database (Click Here)
  • Small demonstration of Service broker for beginners (Click Here)
  • Find Service Broker Object like Contract, Message Type, Service Name, Queue Name in SQL Server (Click Here)
  • Find Service Broker Queue Count in SQL Server (Click Here)
  • Get Transmission queue row count in Service Broker (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

2 thoughts on “Purge target Transmission queue of Service Broker in SQL Server”

Comments are closed.