"Fire and Forget" is a scenario in Service Broker where Initiator starts a conversation -> Sends a message -> End Conversation without considering whether Target received and processed the message. In these scenarios, the Service Broker conversations get piled up which you can see by querying sys.conversation_endpoints.
When you run the query below on the SB enabled database, you will get high number of rows with any state other than CLOSED.
SELECT state_desc,COUNT(*) FROM sys.conversation_endpoints GROUP BY state_desc
In one of the recent issue I worked, we got 48624361 rows with state "DISCONNECTED_INBOUND"
As per BOL (http://msdn.microsoft.com/en-us/library/ms176082.aspx) DI state means: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.
Becauseof this fire and forget SB implementation, SQL Server fails with memory errors after running for 1 hr after a restart:
Since it was a 32-bit SQL Server, Buffer Pool visible is limited to 1.6 GB (approx) and out of this 1.36 GB was stolen by OBJECTSTORE_SERVICE_BROKER as per the DBCC MEMORYSTATUS output dumped in SQL Server error log when 701 error occurred:
Short term solution is to run the script below which cleans up these conversation left orphaned in sys.conversation_endpoints
DECLARE @handle UNIQUEIDENTIFIER DECLARE conv_cur CURSOR FAST_FORWARD FOR SELECT CONVERSATION_HANDLE FROM SYS.CONVERSATION_ENDPOINTS OPEN conv_cur; FETCH NEXT FROM conv_cur INTO @handle; WHILE @@fetch_status = 0 BEGIN END CONVERSATION @handle WITH CLEANUP FETCH NEXT FROM conv_cur INTO @handle; END CLOSE conv_cur; DEALLOCATE conv_cur; GO
WARNING: This script will cleanup all the conversations. So please modify the query to delete only conversations with a particular state Ex. DISCONNECT_INBOUND. Also make sure that you stop you application which uses Service Broker and ensure that all existing messages are processed by the Target else you might loose messages which are not yet processed by the Target.
Long term solution is to modify the Service Broker Implementation to make Initiator wait for response from Target before ending the conversation so that flow will be like this:
1. Initiator sends request2. Target receives request3. Target processes request4. Target sends response5. Initiator receives response6. Initiator processes response7. Initiator ends conversation8. Target receives EndDialog message9. Target ends conversation
This line in incorrect
"FETCH NEXT FROM conversation INTO @handle; "
Should be :
FETCH NEXT FROM conv_cur INTO @handle;
*** Response from Sakthi ***
Thanks Moham for spotting that... Script is updated... Hope that helped!