Service Broker "Fire and Forget Scenario" and Memory Allocation failure
"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 (https://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:
Failed allocate pages: FAIL_PAGE_ALLOCATION 1
Error: 802, Severity: 17, State: 10
There is insufficient memory available in the buffer pool.
Error: 701, Severity: 17, State: 178
There is insufficient system memory to run this query
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:
OBJECTSTORE_SERVICE_BROKER (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 1427064 KB
MultiPage Allocator = 0 KB
Resolution
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 request
2. Target receives request
3. Target processes request
4. Target sends response
5. Initiator receives response
6. Initiator processes response
7. Initiator ends conversation
8. Target receives EndDialog message
9. Target ends conversation