Service Broker may not remove messages after processing

I’ve seen an issue with Service Broker that others may be seeing.  Messages are sent into the service broker, and are processed as normal, and they are removed from the message queue.  However you still see the conversation in the sys.conversation_endpoints DMV in a CONVERSING state instead of a CLOSED state.  There are no records in the sys.transmission_queue which is the very strange part.  There are also no errors when checking with SQL Server Profiler.

 Apparently this is a known issue which they are working on.  The strange thing is that when it happens on my system, it only happens on a single queue in my database.

Currently the only workaround is to do an END CONVERSATION WITH CLEANUP on the conversations.  I’ve written this script which clears out the conversations.  I’ve made it so that it only removes the messages which are for the problem conversation which don’t currently exist in the queue (this queue is not auto processed, there is a service which queries the queue every 30 seconds so there can be a backlog of valid messages in the queue which I don’t want to delete).

declare @i int
set @i = 1
while @i <> 10000
begin
  declare @conversation_handle uniqueidentifier
  declare cur CURSOR for
  SELECT TOP (1000) conversation_handle
  FROM sys.conversation_endpoints
  WHERE NOT EXISTS (SELECT *
  FROM [tcp://AWT/Sonar/Q_ObjectDelete] a
  WHERE a.conversation_handle = sys.conversation_endpoints.conversation_handle)
  AND sys.conversation_endpoints.far_service = 'tcp://AWT/Sonar/Svc_ObjectDelete'
  AND sys.conversation_endpoints.state <> 'CD'
  open cur
  fetch next from cur into @conversation_handle
  while @@fetch_status = 0
  begin
  end conversation @conversation_handle with cleanup
  fetch next from cur into @conversation_handle
  end
  close cur
  deallocate curset @i = @i + 1
endI run this every hour to clean up the bogus records in the sys.conversation_endpoints DMV.

Without cleaning up the sys.conversation_endpoints DMV the tempdb will slowly start to fill up and throw out of space messages while sp_spaceused shows that the tempdb is empty in the same that id did in the other post I did a while back.

Denny