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