-> I was working on a Service broker issue where conversations with State Disconnected_inbound was piling up in Sys.Conversation_Endpoint.
-> Each new conversation that is initiated ends up with a state Disconnected_inbound in Sys.Conversation_Endpoint.
-> DI Disconnected inbound. The remote side of the conversation has issued an END CONVERSATION. The conversation remains in this state until the local side of the conversation issues an END CONVERSATION. An application might still receive messages for the conversation. Because the remote side of the conversation has ended the conversation, an application cannot send messages on this conversation. When an application issues an END CONVERSATION, the conversation moves to the CD (Closed) state.
-> I confirmed with my Client that Service broker is indeed working and that the results are written to the required destination. It is just that these conversations are not getting closed properly.
-> I checked the stored procedures associated with Service broker on that database and was able to see below code,
SELECT TOP 1 @ConversationHandle = @ConversationHandle FROM @Messages;
-> There was a mistake in above code. Instead of ConversationHandle it was wrongly depicted as @ConversationHandle.
-> I changed the above code to below,
SELECT TOP 1 @ConversationHandle = ConversationHandle FROM @Messages;
-> Usage of @ConversationHandle instead of ConversationHandle always results in NULL for @ConversationHandle, which in turn doesn’t end the conversation properly.
-> This resolved the issue. It was a minor mistake, but had huge impact on the overall process.
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.