Only the first message goes to my receive queue

Question

Hi guys

I'm currently trying to utilize the service broker as a log system. The idea is I can easily add or remove tables to the queue system, by using triggers to send messages inform of XML to a queue to be processed later on. The other good this about this is that I use and asynchronous behavior of the service broker to level out CPU resources.

It's going okay. I have been successful in sending and receiving message through the service broker, and parse the XML into a table setup that allowed me to query the log in details. The only problem with this solution is that at that point when ever an insert, update or delete happen on my test table with the system active, it creates a conversation for that change only and ends the conversation right after. This gives a major overhead in creating and ending conversations so it took me 10 minutes to do a 100K isolated inserts.

I then changed the trigger on the table to first look for a open conversation and use that if found, or create a new conversation but not closing it when done. It brought the time down to 2 minutes but only the first message is going through the service broker system and down to my log table. The rest of the messages just sits in my send queue. Why does this happen? And most important: How can I tell the messages to be sent, or call for a flush?

Thank you

Answer 1

any suggestions?

Answer 2

What exactly do you mean by the send queue?  Do you mean the target queue?  Is your log system an activated stored procedure activated for the target queue?

Sorry for all these questions but it might help if you post some simplified code. 

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 3

Sorry for the lack of code.

I have a send service with a send queue where a trigger on the tables that i want to be logged can converse. The create of the send queue is as follows:

CREATE QUEUE logSendQueue
GO

CREATE SERVICE logSendService
ON QUEUE logSendQueue (logcontract)
GO

The trigger converse code is as follows:

if @auditBody <> ''
BEGIN
	DECLARE @h UNIQUEIDENTIFIER
	Declare @CXml varchar(max)

	Set @CXml = @auditBody

	--Locate open conversation
	SET @h = (SELECT TOP(1) [conversation_handle]
	FROM sys.conversation_endpoints with(nolock)
	WHERE [state] = 'co'
	AND far_service = 'logSendService'
	ORDER BY lifetime DESC);

	if(@h IS NULL)
		BEGIN DIALOG CONVERSATION @h FROM SERVICE logSendService TO SERVICE 'logReceiveService' ON CONTRACT logcontract WITH ENCRYPTION=OFF;

	SEND ON CONVERSATION @h MESSAGE TYPE logmessage (@CXml)

	--END CONVERSATION @h;
END   

And lastly the receive queue code:

CREATE QUEUE logReceiveQueue 
 WITH 
 ACTIVATION (
 PROCEDURE_NAME = lms_Log_InsertIntoMasterLog, 
 MAX_QUEUE_READERS = 5, 
 EXECUTE AS SELF )
GO

CREATE SERVICE logReceiveService
ON QUEUE logReceiveQueue (logcontract)
GO

Answer 4

I think found the problem.

When I start a completely new conversation, I get the ID directly. When I look for the newly created conversation, I think I got the wrong conversation_handle back. Looking through my conversation_endpoints table like this:

SELECT * FROM sys.conversation_endpoints with(nolock) WHERE far_service LIKE 'log%'

I can see the I have two open endpoints, which makes sense. One for each service (send/receive). The problem is that was looking through this table with a where clause in the "far_service" that looked for the 'logSendService', by executing the following SQL:

SET @h = (SELECT TOP(1) [conversation_handle]
				FROM sys.conversation_endpoints with(nolock)
				WHERE [state] = ''co''
				AND far_service = ''logSendService''
				ORDER BY lifetime DESC);

What I though this did was to get the sendService end point, but it actually gets the receiveService end point. This was confirmed because the ID i get matches with a endpoint that is not the initiator. If I look for the service with the far_service=logReceiveService, I get my sendService endpoint and it's set as the initiator.

My messages are flowing through the system as expected now.

Answer 5

I'm glad you were able to sort out the issue.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 6

Why do you even try to get the conversation handle from the sys.conversation_endpoints table? It is a lot easier to get it directly from the receive() command. Receive will give you the conversation_handle together with the received message, it's message type name, etc, so you can answer to the message directly to the sender.

declare @h uniqueidentifier,
      @messageTypeName sysname,
      @messageBody varbinary(max);

receive top(1)
   @h = conversation_handle,
   @messageTypeName = message_type_name,
   @messageBody = message_body
from dbo.LogReceiveQueue;

---

SQL expert for JF Hillebrand IT BV - The Netherlands.

Answer 7

Sorry, you're trying to reuse the existing conversation. Then your approach is a correct one.

---

SQL expert for JF Hillebrand IT BV - The Netherlands.

Answer 8

With some testing I can now confirm that the problem was fixed. I have set my solution post as an answer.