 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Other Technologies / Service Broker / December 2006sys.conversation_endpoints growing out of control
We have recently discovered and issue where we had over 15,000,000 rows in sys.conversation_endpoints. This records all have a status of 'closed', but they are not being removed from the table. It is my understanding that if the conversation is 'closed', these rows should be removed in about an hour. The strange thing is all records in sys.converation_endpoinst have a security_timestamp of '1900-01-01 00:00:00.000', does anyone know why this records would not be given a correct date/time? I believe from testing that the internal process cleaning up old records may rely on this field. thanks for your help Hello Jeremy, > We have recently discovered and issue where we had over 15,000,000 > rows in sys.conversation_endpoints. This records all have a status of [quoted text clipped - 5 lines] > correct date/time? I believe from testing that the internal process > cleaning up old records may rely on this field. Are you sending the messages without encryption by chance and are all of the messages in the conversation_group_id closed? Thanks, Kent Tegels, DevelopMentor http://staff.develop.com/ktegels/ I am not using encryption. All records in sys.conversation_endpoints have a state_desc = 'Closed' > Hello Jeremy, > [quoted text clipped - 14 lines] > Kent Tegels, DevelopMentor > http://staff.develop.com/ktegels/ This issue is often caused by one end of the conversation not closing. For the conversation to be completely closed, both endpoints have to issue an end conversation command. Another possibility is that you are running a pre-released version of SQL Server 2005.  SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm > We have recently discovered and issue where we had > over 15,000,000 rows in sys.conversation_endpoints. This records all have [quoted text clipped - 11 lines] > > thanks for your help Roger, We double checked to make sure that we are ending all conversations. Lets assuem we missed something. If a conversation was not closed somehow, wouldn't I see it with a status other then closed in sys.conversation_endpoints? The versions of sql2005= 9.00.2047.00 sql sp1 is installed > This issue is often caused by one end of the conversation not closing. For > the conversation to be completely closed, both endpoints have to issue an [quoted text clipped - 16 lines] > > > > thanks for your help Right - it should be Disconnected Inbound but a lot of people interpret that as closed. Has the conversation lifetime on these conversations expired? Are you running SP1? You can ignore the security date - if you aren't using encryption, the session key isn't created so there isn't a date in this column. SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm > Roger, > We double checked to make sure that we are ending all conversations. Lets [quoted text clipped - 29 lines] >> > >> > thanks for your help Hello Roger Wolter[MSFT], > Right - it should be Disconnected Inbound but a lot of people > interpret that as closed. Has the conversation lifetime on these > conversations expired? Are you running SP1? You can ignore the > security date - if you aren't using encryption, the session key isn't > created so there isn't a date in this column. Right, the status can DI, DO or CD. Reading BOL seems to indicate that the only way a status can go from DI is to end the conversation remotely. DO seems to indicate that was closed locally. CD = DI+DO. then. So, Roger, I *assumed* that Jeremy's reporting of the status was status_descr = 'closed' Jeremy, what values are you actually seeing for status and status_descr. Roger's other answer is why I asked about encryption. Thanks, Kent Tegels, DevelopMentor http://staff.develop.com/ktegels/ To start I am running SP1 sql version 9.00.2047.00 All records in sys.conversation_endpoints have a state = 'CD' and a state_desc='CLOSED'. > Hello Roger Wolter[MSFT], > [quoted text clipped - 18 lines] > Kent Tegels, DevelopMentor > http://staff.develop.com/ktegels/ Hello Jeremy, Any chance that this was an upgrade from an CTP or RC version? Are both/all servers affected? Thanks, Kent Tegels, DevelopMentor http://staff.develop.com/ktegels/ This is not a CTP or RC version. We have other databases on the same server that are running queues and they are removing records from the sys.conversation_endpoints currectly. It seems to be only effecting this one database. Is there any db service broker setting I may not be aware of? We are not using encryption on any of them, but have noticed the security_timestamp is populated on these other dbs. This is the code used: DECLARE @ch uniqueidentifier DECLARE @message xml SET @message = (SELECT 'SStest' AS "StepName",1 AS "direction",99999 AS "procUniqueID" FOR XML Path ('msg')) BEGIN DIALOG CONVERSATION @ch FROM SERVICE [SvDispatchIBGService] TO SERVICE 'SvDispatchService' ON CONTRACT [SvDispatchContract] WITH ENCRYPTION = OFF ; SEND ON CONVERSATION @ch MESSAGE TYPE [SvDispatchMsg] (@message) ; END CONVERSATION @ch ; > Hello Jeremy, > [quoted text clipped - 4 lines] > Kent Tegels, DevelopMentor > http://staff.develop.com/ktegels/ Hello Jeremy, I'm going to defer to Roger on this one. He's the man. Thanks, Kent I guess the question is were you running SP1 when these dialogs were created? Are new conversations still being added? If you clean them up, do they stay gone? What's different about this server? SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm > This is not a CTP or RC version. > [quoted text clipped - 30 lines] >> Kent Tegels, DevelopMentor >> http://staff.develop.com/ktegels/ I was not sure how old these conversations were, so I waited to clean them all up before posting again. I can safely say all conversation records are post SP1, and that after cleaning them up they return. Does anyone know if the internal cleanup process has a threshold rate at which it can remove records? I am wondering if it is possible that were are inserting records at a higher rate then MSSQL Server is archiving the records. Thanks > I guess the question is were you running SP1 when these dialogs were > created? Are new conversations still being added? If you clean them up, do [quoted text clipped - 34 lines] > >> Kent Tegels, DevelopMentor > >> http://staff.develop.com/ktegels/ SQL Server doesn't archive closed conversations. Deletes are done from a background thread so it's possible a heavy load could delay deletes but not for days at a time. You said you have other databases where conversations are being removed successfully so it's worth looking at what's different with this database - different application, stopped queues, etc. You might also want to use profiler to trace the conversation events to make sure the closes look the same as the databases that work. If you can't see any differences you should probably open a case with support services. SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm >I was not sure how old these conversations were, so I waited to clean them > all up before posting again. I can safely say all conversation records [quoted text clipped - 48 lines] >> >> Kent Tegels, DevelopMentor >> >> http://staff.develop.com/ktegels/ I asked the dev team about this and they think it might be this problem: http://blogs.msdn.com/remusrusanu/archive/2006/04/06/570578.aspx SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm >I was not sure how old these conversations were, so I waited to clean them > all up before posting again. I can safely say all conversation records [quoted text clipped - 48 lines] >> >> Kent Tegels, DevelopMentor >> >> http://staff.develop.com/ktegels/ We actully have similar issue when after conversation is closed there are still records in a sys.conversation_endpoints in a CLOSED state. Conversation is closed on both ends but first on the initiator side. I have read an article mentioned and I understand the point but it's still not clear why records stay in the sys.conversation_endpoints? Does it mean that in order to avoid this conversation should be closed first on the target side? > I asked the dev team about this and they think it might be this problem: > http://blogs.msdn.com/remusrusanu/archive/2006/04/06/570578.aspx [quoted text clipped - 51 lines] > >> >> Kent Tegels, DevelopMentor > >> >> http://staff.develop.com/ktegels/ I have repeat the test and ended the conversation first on the target and still have the same issue. This happens on SQL 2005 Express 9.0.1399 > We actully have similar issue when after conversation is closed there are > still records in a sys.conversation_endpoints in a CLOSED state. Conversation [quoted text clipped - 59 lines] > > >> >> Kent Tegels, DevelopMentor > > >> >> http://staff.develop.com/ktegels/ No, they stay around to prevent replay attacks. For example if someone sends a message that says "add $1000 to this bank account", someone could capture the message from the network, send it again and get an extra $1000. To prevent this, we keep the dialog around until the message lifetime has expired - about 30 minutes. If someone replays a message, Service Broker will detect that the dialog is still around and reject the message as a duplicate. After half an hour, if the message is replayed, Service Broker will reject it because its lifetime has expired. This only applies to the target because only the target gets a message that starts a dialog. A message replayed to the imitator will be ignored if the dialog doesn't exist. SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm > We actully have similar issue when after conversation is closed there are > still records in a sys.conversation_endpoints in a CLOSED state. [quoted text clipped - 73 lines] >> >> >> Kent Tegels, DevelopMentor >> >> >> http://staff.develop.com/ktegels/ For others seeing my same issue, he is a link to the solution we adopted some time ago. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=997695&SiteID=1 > I asked the dev team about this and they think it might be this problem: > http://blogs.msdn.com/remusrusanu/archive/2006/04/06/570578.aspx [quoted text clipped - 51 lines] > >> >> Kent Tegels, DevelopMentor > >> >> http://staff.develop.com/ktegels/ This is an update to my Original string, I have had some time to continue testing. After more testing, we have narrowed down our issue - Any time a message is sent to a Q on another DB, the conversation_endpoint row on the target DB is not removed. This occurs regardless of the way the message is sent, i.e. using "send and forget" or if the initiator sends the message, then after receiving the End Dialog message in response, ends the conversation. We can also reproduce this using any DB as the target. Both DB's have a master key, and we're using WITH ENCRYPTION = OFF on the BEGIN DIALOG statement. When a message is sent between Q's on the same DB, the conversation_endpoint rows are removed as expected. Is anyone aware of possible issues caused from sending messages between dbs? > I was not sure how old these conversations were, so I waited to clean them > all up before posting again. I can safely say all conversation records are [quoted text clipped - 43 lines] > > >> Kent Tegels, DevelopMentor > > >> http://staff.develop.com/ktegels/ How long do the conversation last at the target endpoint? They are supposed to stay around for half an hour or so to prevent replay attacks. What is the state of the conversations that are left in the target endpoint? SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm > This is an update to my Original string, I have had some time to continue > testing. [quoted text clipped - 74 lines] >> > >> Kent Tegels, DevelopMentor >> > >> http://staff.develop.com/ktegels/ They never get removed they have have a state 'CD', closed. > How long do the conversation last at the target endpoint? They are supposed > to stay around for half an hour or so to prevent replay attacks. What is [quoted text clipped - 78 lines] > >> > >> Kent Tegels, DevelopMentor > >> > >> http://staff.develop.com/ktegels/ The only thing I'm aware of that causes that is ending the dialog on the initiator side before the target is established. If you aren't running SP1, you might try that. Otherwise, I would recommend opening a case with Customer Support. SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm > They never get removed they have have a state 'CD', closed. > [quoted text clipped - 99 lines] >> >> > >> Kent Tegels, DevelopMentor >> >> > >> http://staff.develop.com/ktegels/ |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.