Good afternoon,
I have a production service broker queue which stopped processing messages about a month ago. After clearing out the messages that had backed up there remains 8 messages that will not leave the queue. I assume it is these messages that are blocking me from disabling or deleting the queue which would hopefully allow the whole system to start up again.
If I select from the queue I see the 8 messages and they all have a message_type_name of "http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog".
If I match these messages up with sys.conversation_endpoints I get a state_desc of "DISCONNECTED_INBOUND"
Using ssbdiagnose on a specific conversation handle I receive the following error: Service broker received an END CONVERSATION message on this conversation. Service Broker will not transmit the message; it will be held until the application ends the conversation.
If I try to end the conversation using:
END CONVERSATION ... WITH CLEANUP;
The query just spins, (currently over 20 min).
I get the impression that my next course of action is the following command:
ALTER DATABASE ... SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
Not a fan of this after reading some of the fallout but if that is the only option then we'll have to deal with it.
So, thoughts on next steps / any other option than the new broker? If additional information is necessary just let me know.
Thanks!