Ending a Conversation

I am attempting to learn Service Broker from Bob Beauchemin's book "A Developer's Guide to SQL Server" - Chapter 11. I'm finding it to be very good but I'm confused over the concept of closing a conversation. Could someone answer the following questions for me?

When a conversation is ended, can the conversation handle that was created when the conversation was created still be used? (I assume not)

Beauchemin says, on page 511, that when a conversation ends, "Any messages still in the queue from the other end of the conversation are deleted with no warning." Does this mean that if I send a message that expects a reply, but I end the conversation, the message is still sent, it is still received by the other endpoint, the other endpoint processes it, but I'll never receive the reply?

Beauchemin says that if no lifetime is specified, the conversation is active for the number of seconds which can be represented by the maximum size of an integer. Does this mean that if I don't specify a lifetime, a conversation is active for many, many years?

Thanks very much.

Amos

I find Bob's book a real treasure chest, covering so much of the new SQL features.

Amos wrote:

1. When a conversation is ended, can the conversation handle that was created when the conversation was created still be used? (I assume not)

Yes and no. After ending a conversation the handle can no longer be used to SEND (or MOVE), but it can still be used to END again (a no-op) or to END ... WITH CLEANUP (e.g. in case messages are stuck in transmission_queue and you know the're never going to get trough). Both sites have to END the conversation and once the EndDialog message is received from the other side, the conversation endpoint handle is deleted so all verbs (SEND,END, MOVE) will fail because the conversation handle value is invalid. the target side might keep around a handle for up to 30 minutes after it was closed (ENDed by both sides) for security reasons (prevent a replay attack).

Amos wrote:

2. Beauchemin says, on page 511, that when a conversation ends, "Any messages still in the queue from the other end of the conversation are deleted with no warning." Does this mean that if I send a message that expects a reply, but I end the conversation, the message is still sent, it is still received by the other endpoint, the other endpoint processes it, but I'll never receive the reply?

Yes, you'll not receive any reply, since you already declared that you're no longer interested in the result of this conversation (by ENDing it). Even worse, if the conversation is ended with an error, you'll never know that about error, including system errors like 'SEND permission denied'. See http://blogs.msdn.com/remusrusanu/archive/2006/04/06/570578.aspx for more on this.

Amos wrote:

3. Beauchemin says that if no lifetime is specified, the conversation is active for the number of seconds which can be represented by the maximum size of an integer. Does this mean that if I don't specify a lifetime, a conversation is active for many, many years?

That is correct. In theory, your conversation will expire after many, many years (68, I believe). In practice, I doubt any application written today will be around after 60 years...

HTH,
~ Remus

END CONVERSATION WITH CLEANUP, Is this bad? Records pile up in queues without it.

I have read from a variety of sources that using the "WITH CLEANUP" option on a "END CONVERSATION" statement is bad and unnecessary. (Question #1) Is this true?

My code does not work properly if I don't use the "WITH CLEANUP" option. My code leaves closed conversation records in the queues if I leave out the "WITH CLEANUP" option. The "END CONVERSATION" statement is executing properly and flagging the conversation record as closed but the records don't get deleted. All the messages are going back and forth properly too.

My code is based on the HelloWorld ServiceBroker sample which does not use "WITH CLEANUP". When I run the sample scripts everything works great and the conversation records are deleted. However, this sample does not uses an activation stored procedure to receive messages and respond with the results. When I copy and paste the receive messages sample code into an activation stored procedure is when the problem comes up. It's the same code! (Question #2) Why am I getting different results depending how the code is executed/activated?

This is could be a tough one. I just hope somebody else has seen it too and figured it out. Thanks for the help!

Thanks,
Greg Van Mullem

? "Bad" and "unnecessary" are interesting labels, because they can be applied so differently to various scenarios. If WITH CLEANUP were not necessary in any scenario, I seriously doubt it would have been included in the product. But is it right for every scenario? No. Essentially, according to the documentation and Roger Wolter's book (which you should definitely buy a copy of if you're working with SSB), those closed conversations are supposed to stick around for a while, to avoid certain types of replay attacks. But they should disappear after a while. As for the WITH CLEANUP, it is bad if your conversation is truly a conversation (i.e., a two-way message exchange), because when one side ends the conversation and uses that option, the other side doesn't find out about it. And that can certainly be bad in many cases. On the other hand, if you're only doing one-way transmission, that may or may not be an issue. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Greg Van Mullem@.discussions.microsoft.com> wrote in message news:1c9bb57b-0113-4fd6-ba81-14c686a99b16@.discussions.microsoft.com... I have read from a variety of sources that using the "WITH CLEANUP" option on a "END CONVERSATION" statement is bad and unnecessary. (Question #1) Is this true?My code does not work properly if I don't use the "WITH CLEANUP" option. My code leaves closed conversation records in the queues if I leave out the "WITH CLEANUP" option. The "END CONVERSATION" statement is executing properly and flagging the conversation record as closed but the records don't get deleted. All the messages are going back and forth properly too.My code is based on the HelloWorld ServiceBroker sample which does not use "WITH CLEANUP". When I run the sample scripts everything works great and the conversation records are deleted. However, this sample does not uses an activation stored procedure to receive messages and respond with the results. When I copy and paste the receive messages sample code into an activation stored procedure is when the problem comes up. It's the same code! (Question #2) Why am I getting different results depending how the code is executed/activated?This is could be a tough one. I just hope somebody else has seen it too and figured it out. Thanks for the help!Thanks,Greg Van Mullem|||

Adam,

Thanks for the info. This is exactly what I was looking for. I will be removing the "WITH CLEANUP" clause from my code now that I know it's not necessary.

Also, I have Roger Wolters book and your book "Pro SQL Server 2005". Both are books excellent! I found the XML section in your book to be especially helpful in setting up my Service Broker messages.

Thanks,
Greg Van Mullem

end conversation causing blocks

Hi

I was trying to clean up some conversation in Service Broker and caused alot of blocking that I seem to unable to kill. there was 1 conversation that I was not able to end, so I wanted to restart sql service, But I can't even restart the SQL service. I get the following in Event Viewer

Timeout occurred while waiting for latch: class 'SERVICE_BROKER_TRANSMISSION_INIT', id 00000001A2B03540, type 2, Task 0x0000000000C2EDA8 : 0, waittime 5400, flags 0xa, owning task 0x00000002DEBCA5C8. Continuing to wait.

Has anyone come across this

thanks

Paul

have you tried to clear the conversation?

declare @.conversation uniqueidentifier

while exists (select 1 from sys.conversation_endpoints )

begin

set @.conversation = (select top 1 conversation_handle from sys.conversation_endpoints )
print @.conversation
end conversation @.conversation with cleanup

end

end conversation : security context could not be retrieved

HI

I have a service broker setup between 2 remote server. The message send does get sent to the target, but I am having a problem where the end conversation message from the target is failing. I did a trace on both the target and the source server. here's what I found

On the Target Server:

on Broker: Message undeliverable This message could not be delivered because it is a duplicate

On the Source Server

on Broker: Message undeliverable This message could not be delivered because the security context could not be retrieved,

I do not understand why the message is delivered, but the end conversation message is not getting thru. On the Target transmission_queue. I have millions of messages like this

conversation_handle to_service_name is_end_of_dialog message_body transmission_status

E0C69E8F-37E9-DB11-AB7A-00145E7A209C source 1 NULL

I reinstalled the broker several times, but always get this problem.

thanks

Paul

Each Broker: Message undeliverable This message could not be delivered because the security context could not be retrieved event should also be accompanied by an event of type Security Audit/Audit Broker Conversation. That info should explain why the 'security context could not be retrieved'.|||

Hi

I did trace with audit broker conversation, there is this event after it, but there are not error message? I recently ungraded to sp3, could this be a problem

thanks

Paul

|||

HI

I was reading the other thread about no security header and check the time in the enqueue time, which was ahead of the server time

the last enqueue_time on the target was

2007-04-13 22:27:16

but when I did a select getdate()

I get 2007-04-13 20:24:03

how can the enqueue time be in the future?

thanks

Paul

|||what is the event subclass?|||Because of the distributed nature of messaging, all SSB times are UTC|||

it is : no security header. but I check the time on both machines, they are the same. If they were different, the send message would have the same error as the end dialog message?

Paul

|||Check if UTC time is the same, using getutcdate(). A problem could be if latest daylight savings patch was applied on one machine and not on other.|||

hum? I did a getutcdate() on both machines, they show the same time. If the time was the problem, wouldn't the sent message fail too? But I am unable to verify whether KB931836 was installed on one of the machines. I'll need to ask the SA on Monday. But the date change patch you bring up is an interesting point. If one of the machine does not have the patch, it would explain another problem we had last week regarding insert records from another machines where its was 1 hr late. thanks for the help. we'll see monday

Paul

|||

HI

I still have this problem, I was told that the servers where both patched with sp2 several weeks ago. I did more tracing on the sender and target. It seems the end dialog message from the target was successfully pocessed by the sender queue. Only after when the queue process the end dialog do I see "security context could not be retrieved" in the trace. Does Service Broker send another message to the the target after the sender ends the conversation?

Paul

|||

I met the same problem in our environment. our environment is:

Sender: SQL 2005 SP1, 64Bit CPU, windows 2003, 32G memory (currently only has SSB run on it, seems use more than 20g memory and about 40 % cpu resource)

Target: SQL 2005 SP1 ,32bit CPU, widnows 2003, 16G? memory.

the same situation, when the sender END CONVERSATION... i get the "security context could not be retrieved" from profiler90.

end conversation : security context could not be retrieved

HI

I have a service broker setup between 2 remote server. The message send does get sent to the target, but I am having a problem where the end conversation message from the target is failing. I did a trace on both the target and the source server. here's what I found

On the Target Server:

on Broker: Message undeliverable This message could not be delivered because it is a duplicate

On the Source Server

on Broker: Message undeliverable This message could not be delivered because the security context could not be retrieved,

I do not understand why the message is delivered, but the end conversation message is not getting thru. On the Target transmission_queue. I have millions of messages like this

conversation_handle to_service_name is_end_of_dialog message_body transmission_status

E0C69E8F-37E9-DB11-AB7A-00145E7A209C source 1 NULL

I reinstalled the broker several times, but always get this problem.

thanks

Paul

Each Broker: Message undeliverable This message could not be delivered because the security context could not be retrieved event should also be accompanied by an event of type Security Audit/Audit Broker Conversation. That info should explain why the 'security context could not be retrieved'.|||

Hi

I did trace with audit broker conversation, there is this event after it, but there are not error message? I recently ungraded to sp3, could this be a problem

thanks

Paul

|||

HI

I was reading the other thread about no security header and check the time in the enqueue time, which was ahead of the server time

the last enqueue_time on the target was

2007-04-13 22:27:16

but when I did a select getdate()

I get 2007-04-13 20:24:03

how can the enqueue time be in the future?

thanks

Paul

|||what is the event subclass?|||Because of the distributed nature of messaging, all SSB times are UTC|||

it is : no security header. but I check the time on both machines, they are the same. If they were different, the send message would have the same error as the end dialog message?

Paul

|||Check if UTC time is the same, using getutcdate(). A problem could be if latest daylight savings patch was applied on one machine and not on other.|||

hum? I did a getutcdate() on both machines, they show the same time. If the time was the problem, wouldn't the sent message fail too? But I am unable to verify whether KB931836 was installed on one of the machines. I'll need to ask the SA on Monday. But the date change patch you bring up is an interesting point. If one of the machine does not have the patch, it would explain another problem we had last week regarding insert records from another machines where its was 1 hr late. thanks for the help. we'll see monday

Paul

|||

HI

I still have this problem, I was told that the servers where both patched with sp2 several weeks ago. I did more tracing on the sender and target. It seems the end dialog message from the target was successfully pocessed by the sender queue. Only after when the queue process the end dialog do I see "security context could not be retrieved" in the trace. Does Service Broker send another message to the the target after the sender ends the conversation?

Paul

|||

I met the same problem in our environment. our environment is:

Sender: SQL 2005 SP1, 64Bit CPU, windows 2003, 32G memory (currently only has SSB run on it, seems use more than 20g memory and about 40 % cpu resource)

Target: SQL 2005 SP1 ,32bit CPU, widnows 2003, 16G? memory.

the same situation, when the sender END CONVERSATION... i get the "security context could not be retrieved" from profiler90.

end conversation : security context could not be retrieved

HI

I have a service broker setup between 2 remote server. The message send does get sent to the target, but I am having a problem where the end conversation message from the target is failing. I did a trace on both the target and the source server. here's what I found

On the Target Server:

on Broker: Message undeliverable This message could not be delivered because it is a duplicate

On the Source Server

on Broker: Message undeliverable This message could not be delivered because the security context could not be retrieved,

I do not understand why the message is delivered, but the end conversation message is not getting thru. On the Target transmission_queue. I have millions of messages like this

conversation_handle to_service_name is_end_of_dialog message_body transmission_status

E0C69E8F-37E9-DB11-AB7A-00145E7A209C source 1 NULL

I reinstalled the broker several times, but always get this problem.

thanks

Paul

Each Broker: Message undeliverable This message could not be delivered because the security context could not be retrieved event should also be accompanied by an event of type Security Audit/Audit Broker Conversation. That info should explain why the 'security context could not be retrieved'.|||

Hi

I did trace with audit broker conversation, there is this event after it, but there are not error message? I recently ungraded to sp3, could this be a problem

thanks

Paul

|||

HI

I was reading the other thread about no security header and check the time in the enqueue time, which was ahead of the server time

the last enqueue_time on the target was

2007-04-13 22:27:16

but when I did a select getdate()

I get 2007-04-13 20:24:03

how can the enqueue time be in the future?

thanks

Paul

|||what is the event subclass?|||Because of the distributed nature of messaging, all SSB times are UTC|||

it is : no security header. but I check the time on both machines, they are the same. If they were different, the send message would have the same error as the end dialog message?

Paul

|||Check if UTC time is the same, using getutcdate(). A problem could be if latest daylight savings patch was applied on one machine and not on other.|||

hum? I did a getutcdate() on both machines, they show the same time. If the time was the problem, wouldn't the sent message fail too? But I am unable to verify whether KB931836 was installed on one of the machines. I'll need to ask the SA on Monday. But the date change patch you bring up is an interesting point. If one of the machine does not have the patch, it would explain another problem we had last week regarding insert records from another machines where its was 1 hr late. thanks for the help. we'll see monday

Paul

|||

HI

I still have this problem, I was told that the servers where both patched with sp2 several weeks ago. I did more tracing on the sender and target. It seems the end dialog message from the target was successfully pocessed by the sender queue. Only after when the queue process the end dialog do I see "security context could not be retrieved" in the trace. Does Service Broker send another message to the the target after the sender ends the conversation?

Paul

|||

I met the same problem in our environment. our environment is:

Sender: SQL 2005 SP1, 64Bit CPU, windows 2003, 32G memory (currently only has SSB run on it, seems use more than 20g memory and about 40 % cpu resource)

Target: SQL 2005 SP1 ,32bit CPU, widnows 2003, 16G? memory.

the same situation, when the sender END CONVERSATION... i get the "security context could not be retrieved" from profiler90.

sql

End 35 Million conversations quickly - production issue!

We have a system that has 35 million conversations piled up. We didn't know to explicitly end the conversation once the processing has completed. Oops. Now, our production box has 35 mm sitting in the table, and we have run into the problem where the amount in sys.conversation_endpoints has exceeded memory and they are being dumped into tempdb, which is killing our disk space, thus bringing the box down. We have fixed the code to end the conversations, but we now have to end the conversations in a hurry. If we select one by one out of the table and end the conversation via END CONVERSATION, it is slow. Very slow. It will finish in a few months. :(

Does anyone know how to get rid of these conversations in a hurry? All of the messages have been applied to our system, so killing the conversations will (should) have no affect on the processed data. Something like a TRUNCATE statement?

Thank you so much in advance,

John Hennesey

The best way is to END CONVERSATION in batches and let it run. Make sure you batch 100-200 ENDs in one transaction batch for better performance.

ALTER DATABSE ... SET NEW_BROKER has the risk of distrbing existing, active conversations. Also, in RTM and SP1 is just as slow as a batch of END CONVERSATION ... WITH CLEANUP, since that what it does internally.

If none of these work for you, then if you dare installing SP2 CTP, the internals of ALTER DATABASE ... SET NEW_BROKER have changed to truncate and will do what you need, fast. You will have to suspend the production system for a short while and make sure there are no active dialogs, to prevent active dialogs from being affected. also, if your routes use the broker_instance, you will have to update them after the NEW_BROKER operation.

HTH,
~ Remus

|||

Remus -

Thank you very much for your response. We did exactly what you said and it worked. It was just a bit slower than what we needed. It's good to know the CTP will have another option for handling this - unfortunately since this is a production system CTP isn't an option. We split it up by queue name and ran 10 queries in parallel to end the conversations a little quicker. It took about 24 hours, and with the code changes we are in good shape.

Thank you for your response,

John