SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Service broker filling up Tempdb


Service broker filling up Tempdb

Author
Message
emile.milne
emile.milne
SSC Veteran
SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)

Group: General Forum Members
Points: 223 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
Nils Gustav Stråbø
Nils Gustav Stråbø
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3575 Visits: 3575
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?
emile.milne
emile.milne
SSC Veteran
SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)

Group: General Forum Members
Points: 223 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,
Nils Gustav Stråbø
Nils Gustav Stråbø
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3575 Visits: 3575
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?
emile.milne
emile.milne
SSC Veteran
SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)

Group: General Forum Members
Points: 223 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
emile.milne
emile.milne
SSC Veteran
SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)

Group: General Forum Members
Points: 223 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
Nils Gustav Stråbø
Nils Gustav Stråbø
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3575 Visits: 3575
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?
emile.milne
emile.milne
SSC Veteran
SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)

Group: General Forum Members
Points: 223 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/
emile.milne
emile.milne
SSC Veteran
SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)

Group: General Forum Members
Points: 223 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
Nils Gustav Stråbø
Nils Gustav Stråbø
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3575 Visits: 3575
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.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search