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 12»»

Service broker filling up Tempdb Expand / Collapse
Author
Message
Posted Friday, October 19, 2012 1:58 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:22 AM
Points: 63, Visits: 224
I’m new to SB. Never had issues with it previously.
I have 2 service broker tasks/queues one works fine and I never see it appear when I run
select * from sys.conversation_endpoints
order by [lifetime]
But a new broker task/queue was set up a few weeks ago. Which always appears in this query. It always has a state of ‘DI’ which would indicate that it is not closing the conversation properly. I have checked and there is only 1 SB contract on in the db. Should there be one for each process.
If the conversation closing is the issue how do I check and resolve?
Thanks
Post #1374671
Posted Friday, October 19, 2012 2:06 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:10 AM
Points: 1,869, Visits: 3,457
DI means that only the target service has issued an END CONVERSATION.

In a conversation, both the initiator and the target must call END CONVERSATION. That means that you need to handle message types 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' and 'http://schemas.microsoft.com/SQL/ServiceBroker/Error' and respond with an END CONVERSATION when one of these messages are received. This must be handled by both initiator and target.

How are you receving and processing messages? Using activation procedures, or in some other way?
Post #1374674
Posted Friday, October 19, 2012 3:04 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:22 AM
Points: 63, Visits: 224
If you are referring to how are the queues dealt with, they are processed via a sproc. Which does have logic to capture 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' and end the converstaion with cleanup. As I pointed out I’m new to SB and even I know that with clean up in code is not a good option. Do I have a problem with the conversation, how to get to the bottom of it?

Additonal it is actually both processes which have the DI issue one just runs a lot more frequently than the other,
Post #1374696
Posted Friday, October 19, 2012 4:05 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:10 AM
Points: 1,869, Visits: 3,457
Is it the target service that first calls END CONVERSATION?

Also, would it be possible for you to post the activation procedures and the definition for service broker objects (message types, contracts, services, queues)?

By the way; Are there any rows in sys.transmission_queue?
Post #1374713
Posted Friday, October 19, 2012 9:24 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:22 AM
Points: 63, Visits: 224
Thanks for your posts, can I get back to you mid next week with the SB setup.
I can see that I am getting the http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog
Message returned, the sproc is designed to end the conversation with cleanup at this point. So the conversations are NOT hanging about.
I’m working with a one-way conversation.
sys.conversation_endpoints is getting cleared out.
sys.transmission_queue is empty
Post #1374888
Posted Wednesday, October 24, 2012 7:58 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:22 AM
Points: 63, Visits: 224
SB waittype is either sleep_task or Broker_Flush. This would indicate to me that it is flushing in memory to tempdb. Hence my problem. How do I stop this
Post #1376496
Posted Wednesday, October 24, 2012 8:07 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:10 AM
Points: 1,869, Visits: 3,457
I really need to see the activation procedures to hav any chance of finding out why the conversations are in DI state.

There is no such thing as one way conversation. Both initiatopr and target has to handle clean up, and I suspect that the initiator is not doing so.

Can you post, or IM me, the activation procedure?
Post #1376502
Posted Thursday, October 25, 2012 2:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:22 AM
Points: 63, Visits: 224
Not exactly my code but it is a set up like this

DECLARE @dh UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @dh
FROM SERVICE [Initiator]
TO SERVICE N‘Target’;
SEND ON CONVERSATION @dh;
END CONVERSATION @dh;

wrapped in a cursor. It is basically identical to the above.

http://rusanu.com/2006/04/06/fire-and-forget-good-for-the-military-but-not-for-service-broker-conversations/
Post #1376837
Posted Thursday, October 25, 2012 2:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:22 AM
Points: 63, Visits: 224
It is then ended by the target with

Cursor deallocated.


END

IF @message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @dh WITH CLEANUP
END
Post #1376844
Posted Thursday, October 25, 2012 2:56 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:10 AM
Points: 1,869, Visits: 3,457
That is probably your problem. You should not use WITH CLEANUP in you activation procedures.

From BOL (END CONVERSATION):

Removes all messages and catalog view entries for one side of a conversation that cannot complete normally. The other side of the conversation is not notified of the cleanup. Microsoft SQL Server drops the conversation endpoint, all messages for the conversation in the transmission queue, and all messages for the conversation in the service queue. Administrators can use this option to remove conversations which cannot complete normally. For example, if the remote service has been permanently removed, an administrator can use WITH CLEANUP to remove conversations to that service. Do not use WITH CLEANUP in the code of a Service Broker application. If END CONVERSATION WITH CLEANUP is run before the receiving endpoint acknowledges receiving a message, the sending endpoint will send the message again. This could potentially re-run the dialog.


Post #1376847
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse