April 19, 2013 at 8:34 am
Hello everyone. Hope you are all having a great Friday.
Earlier this week I noticed that the number of items in the sys.conversation_endpoints table for our databases is getting rather large. 1-2million records depending upon the database. What I cannot figure out is why.
To start with, here is the counts from the sys.conversation_endpoints table:
StateTOTAL
Total - 1217855
CONVERSING - 1191883
DISCONNECTED_INBOUND - 25407
CLOSED - 547
STARTED_OUTBOUND - 18
Now, as it shows above, there is A LOT of messages in a CONVERSING state. I cannot figure out why. The maximum number of messages in any one of 7 queues we have is just under 300, and the majority are at 0 messages sitting in the queue. BOL (http://msdn.microsoft.com/en-us/library/ms176082(v=sql.90).aspx) states that the CONVERSING state is:
"Conversing. The conversation is established, and both sides of the conversation may send messages. Most of the communication for a typical service takes place when the conversation is in this state."
However, I don't see where we have 1.2 million conversations happening.
In looking around, I found an article by Remus Rusanu about "fire and forget" found here: http://rusanu.com/2006/04/06/fire-and-forget-good-for-the-military-but-not-for-service-broker-conversations/. However, it doesn't look like in our system, this is the method we are following.
Here is a general setup for our system.
To send a message, we use the following code:
BEGIN DIALOG CONVERSATION @ConversationHandle
FROM SERVICE CompEngineService
TO SERVICE 'CompEngineService'
ON CONTRACT CompEngineContract;
SEND ON CONVERSATION @ConversationHandle
MESSAGE TYPE CompEngineMessageType(@TaskMessage)
We use SB to call a stored procedure on the other side of the convesation. So each of the conversations use an activation stored procedure. Those procedures look like this:
DECLARE @TaskMessage xml
DECLARE @Conversation_Handle uniqueIdentifier
BEGIN TRY
WAITFOR (RECEIVE top(1) @TaskMessage = Message_body
, @Conversation_Handle = conversation_handle
FROM [GenericProcessQueue]
), TIMEOUT 10000
if @@RowCount = 0 OR @taskMessage
return
<<< Fun logic goes here >>>
end conversation @conversation_handle
END TRY
BEGIN CATCH
<< LOG Conversation and Task Message to error table >>
END CONVERSATION @Conversation_handle
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRAN
END
END CATCH
Does anyone have a clue as to what I am missing here. When I test the code individually, everything seems to be working as expected.
Running SQL 2005 SP4 64 bit on 2003 window server machines.
Thanks,
Fraggle
April 20, 2013 at 10:55 am
Fraggle-805517 (4/19/2013)
Hello everyone. Hope you are all having a great Friday.....
....BEGIN CATCH
<< LOG Conversation and Task Message to error table >>
END CONVERSATION @Conversation_handle
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRAN
END
END CATCH
Looks like a bad logic, look at CATCH block, first you have a END CONVERSATION statement and then ROLLBACK which rollback also the END CONVERSATION statement. And the same is true for << LOG Conversation and Task Message to error table >>
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply