Hello,
Been reading up on this Service Broker issue where the sysxmitqueue unexpectedly grows and the best thing I can find other than recreating a new service broker which I don't want to do is using the below sql statement. The problem though is that the sysxmitqueue table has grown so large this statement takes way too long. I ran it first in my lab trying to only do 50k records first and it ran for over 17hrs and still going. Any other suggestions? Appreciate the help.
--------------------------------
declare @conversationHandle uniqueidentifier
while (1 = 1)
begin
select @conversationHandle = conversation_handle from sys.conversation_endpoints
where 'CLOSED' = state_desc
if @@rowcount = 0 break
end conversation @conversationHandle with cleanup
end