Service Broker may not remove messages after processing

Published On: 2008-09-11By:

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 cur
set @i = @i + 1
end
I 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


Contact the Author | Contact DCAC

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers    VMWare Partner
Microsoft Certified Master    Microsoft MVP
Share via