Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Service Broker sys.conversation_endpoints number climbing Expand / Collapse
Author
Message
Posted Friday, April 19, 2013 8:34 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Sunday, July 27, 2014 7:58 PM
Points: 727, Visits: 1,411
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:

State TOTAL
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
Post #1444453
Posted Saturday, April 20, 2013 10:55 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:47 AM
Points: 223, Visits: 1,718
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 >>
Post #1444723
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse