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