October 19, 2012 at 1:58 am
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
October 19, 2012 at 2:06 am
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?
October 19, 2012 at 3:04 am
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,
October 19, 2012 at 4:05 am
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?
October 19, 2012 at 9:24 am
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
October 24, 2012 at 7:58 am
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
October 24, 2012 at 8:07 am
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?
October 25, 2012 at 2:35 am
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.
October 25, 2012 at 2:53 am
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
October 25, 2012 at 2:56 am
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.
October 25, 2012 at 4:39 am
Is it as simple as just giving rid of the ‘with clean up’ or does it need a rewrite as the link suggests as it is a fire and forget from the initiator.
October 25, 2012 at 5:00 am
Is it as simple as just giving rid of the ‘with clean up’ or does it need a rewrite as the link suggests as it is a fire and forget from the initiator.
October 25, 2012 at 5:07 am
Impossible to say without testing. Implement this change on a development server and see if it works. That is the only advice I can give without seeing you actual code.
October 29, 2012 at 2:43 pm
Can two service broker queues use the same contract.
November 5, 2012 at 4:26 am
Bobby Glover (10/29/2012)
Can two service broker queues use the same contract.
It's services that you connect a contract to, not the queue. When you have a conversation between 2 services in the same database, it is even very likely that 2 queues use the same contract.
So yes, you can re-use the same contract for multiple services.
Also, you can create multiple services connected to one and the same queue. Since each service can have one or more contracts and there doesn't need to be a connection between the contracts on these services, you can also have messages from multiple contracts going into one and the same queue.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply