atabase just grows and grows.

Question

We have a customer whos database just grows and grows. Not the customers own tables, but the:
sys.sysconvgroup
sys.sysdesend
sys.sysdercv
And these tables are linked to the Service Broker, and according to http://msdn2.microsoft.com/en-us/library/ms179503.aspx these tables exists in every database and are used by the Service Broker.
Now to my questions =)
HOW do I delete rows from these tables? How come these tables hust grows and grows, could it be any setting in the SQL 2005 Server or is it the customer who has programmed his application wrong?

Answer 1

HOW do I delete rows from these tables? How come these tables hust grows and grows, could it be any setting in the SQL 2005 Server or is it the customer who has programmed his application wrong?

You are correct that the likely cause is improper Service Broker programming (e.g. "fire and forget" pattern).  Check sys.conversation_endpoints to see if you have a lot of disconnected conversations.  Many conversations might be a symptom of the app not closing conversations, resulting in leaked conversation handles.

You can execute a END CONVERSATION...WITH CLEANUP to forcibly terminate conversations that were not properly closed by the application.  However, note that this will also delete undelivered messages.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 2

Hi Bob,

Using Query Notifications will leak these conversations handles even on correct usage. Alex, the poster of the problem reported originally in newsgroups, found a good workaround. He created a DDL trigger (http://msdn2.microsoft.com/en-US/library/ms191438(SQL.90).aspx)  for DROP SERVICE. In this trigger he iterates with a cursor for the stranded conversations and cleans then up. Here is a query that the cursor could be based on:
SELECT conversation_handle
      FROM sys.conversation_endpoints ep
      JOIN sys.services s on ep.service_id = s.service_id
      WHERE s.name = N'http://schemas.microsoft.com/SQL/Notifications/QueryNotificationService'
      AND ep.far_service = N'<service being dropped>'

---

Iric Wen

TechNet Community Support

Answer 3

As was said, the most likely cause is incorrect programming of the service broker handler routine(s). To give you some insight what is going on you could have a look at sys.conversation_endpoints. The column of interest is 'status':

• If most conversations have a status of 'CO' (Conversing), no end conversation has been issued on these conversations. If those conversations are not active anymore (the definition of an 'active' conversation depends on the functionality of the application; some applications are designed to use short running conversations, others are meant to stay active for long periods, up to days, weeks, months or even years. So don't assume that all conversations in state 'CO' are to be closed!) an 'end conversation @conversation_handle' should have been issued after the last message got processed. i.e. the handler processing that last message must issue a call to end conversation after the last message. If a conversation is 'active' but nothing happens any more, have a look at sys.service_queues to see if either of the queues got disabled due to 5 consecutive errors and to see if activation is setup correctly and active (very rarely the queue monitor fails to start even if everything is setup correctly, so you can try to disable auto-activation and then re-enable it again to kick start the queue). If that does not resolve the issue, have a look at sys.transmission_queue to see if any messages failed to be delivered, each message in sys.transmission_queue has a value describing the reason why the message could not be delivered.
• For conversations that have a status of 'DI' (Disconnected Inbound), a call to 'end conversation' has been issued at one end of the conversation, but the handler procedure at this end did not properly respond to either the "http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog" or the "http://schemas.microsoft.com/SQL/ServiceBroker/Error" message: each received message of these types should be responded to by at least a call to 'end conversation'. Have a look at the contents of the queue to see if the EndDialog messages are still there. If so, the handler may not have come top processing those enddialog messages yet. This may be because of several reasons: f.e. the queue may be disabled or the handler may not be allowed to run. You should (re-)enable the handler responsible for processing those messages. If the messages are not there (anymore), the handler routine has either got an error in it, by which it forgets to end the conversations or the messages can not be delivered because the queue got disabled at some point (you can see this by looking at sys.service_queues).
• If most conversation have a status of 'DO' (Disconnected Outbound), a call to end conversation has been issued, but the EndDialog message most likely could not be delivered at the other end. Have a look at the entries in sys.transmission_queue to see why the messages can not be delivered, resolve the problem and the processes should clear out the queues automatically.

A word of warning: the "end conversation @handle with cleanup" suggested by Dan Guzman is an administrative tool used to recover from a situation that the system otherwise can not recover from itself. You should never have to use this in a correctly programmed solution. As Dan wisely added: when using "end conversation with cleanup", you may inadvertently delete messages from the system that should still have been processed.

The bug that Iric Wen mentions applies only when your application uses query notification. You haven't indicated this to be the case, so this may or may not apply to your client's database. But if it does, the suggested work around will fix the problem for future conversations. You still will have to do an "end conversation" for all conversations already left stranded.

---

SQL expert for JF Hillebrand IT BV - The Netherlands.