SQL Server encounters memory pressure and generate 701 Error due to incorrect configuration of Service Broker
Here is one of the rare scenario on SQL Server memory issue (701 error) caused due service broker misconfiguration or incorrectly used, causing memory object OBJECTSTORE_SERVICE_BROKER to grow and not releasing memory back.
Below is the memory error, we observed in SQL Server error log followed by DBCC MEMORYSTATUS output.
You will see below error messages in SQL Server error log:
2010-02-17 07:28:43.06 Server Failed allocate pages: FAIL_PAGE_ALLOCATION 1
2010-02-17 07:28:43.06 spid14s Error: 701, Severity: 17, State: 123.
2010-02-17 07:28:43.06 spid14s There is insufficient system memory to run this query.
2010-02-17 07:28:43.06 spid12s Error: 802, Severity: 17, State: 11.
2010-02-17 07:28:43.06 spid12s There is insufficient memory available in the buffer pool.
In DBCC MEMORYSTATUS we find:
Memory Manager
VM Reserved = 1650436 KB
VM Committed = 1649556 KB
AWE Allocated = 0 KB
Reserved Memory = 1024 KB
Reserved Memory In Use = 0 KB
MEMORYCLERK_SQLBUFFERPOOL (Total)
VM Reserved = 1632340 KB
VM Committed = 1632340 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 0 KB
MultiPage Allocator = 400 KB
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 = 1523880 KB -->Consuming most of the memory
MultiPage Allocator = 0 KB
The Single Page allocator value for OBJECTSTORE_SERVICE_BROKER object looks interesting and uncommon causing BPOOL exhaustion consuming almost 1.5 GB.
This hints problem towards service broker. Further to go indepth in troubleshooting service broker gathered the information from the view:
select state,COUNT(*) from sys.conversation_endpoints group by state
There were millions of conversations in the view:
count
--------
20930578
“90% of the conversation was in conversing state”
Was checking the service broker configurations and observed that initiator and target was configured on the same database. The service broker was configured to temporarily save the application messages to improve application performance.
The queue was set in auto mode i.e. the procedure at the target would be called automatically to pick up the message from the target queue. Checked the profiler traces and found that there was no call to the target procedure and that seemed to be the cause of the problem.
Further dived deeper into this issue and was successful in reproducing the issue using the steps below:
Repro steps
===========
Note: Intent of the below mentioned repro is just to show that when the stored procedure at the target gets invalidated then the messages get stuck in the queue causing OBJECTSTORE_SERVICE_BROKER object to keep growing.
ALTER DATABASE DBName SET ENABLE_BROKER
create message type req1 AUTHORIZATION dbo validation= None
create message type req2 AUTHORIZATION dbo validation= None
create queue senderqueue
create queue receiverqueue
create contract testcontract (req1 sent by initiator, req2 sent by target)
create service sendreqservice on queue senderqueue(testcontract)
create service receivereqservice on queue receiverqueue(testcontract)
select name,service_broker_GUID from sys.databases where is_broker_enabled=1
create route testroute with service_Name='sendreqservice', broker_instance='CB496106-EC67-41A9-8F01-3EDFE58FB01B', address='TCP://localhost:1433'
create table temp_table (message char(10))
create procedure sp_processqueue
as
begin
Declare @message char(20)
declare @message_type_name varchar(20)
declare @conversationhandle uniqueidentifier
begin
receive top(1)
@conversationhandle =conversation_handle,
@message_type_name=message_type_name,
@message=message_body
from dbo.receiverqueue
if @message_type_name='req1'
begin
insert into temp_table values(@message);
end
--end conversation @conversationhandle with cleanup;
end
end
Alter queue receiverqueue with activation(status=ON,procedure_name = SP_processqueue,execute as owner)
Declare @message char(10);
declare @conversationhandle uniqueidentifier
set @message='hi'
begin dialog conversation @conversationhandle
from service sendreqservice
to service 'receivereqservice'
on contract testcontract
with encryption =off;
send on conversation @conversationhandle
message type req1
(@message);
--end conversation @conversationhandle
Now let’s invalidate the procedure to create the problem:
1. Change the procedure code somewhere and make it invalid:
alter procedure sp_processqueue
as
begin
Declare @message char(20)
declare @message_type_name varchar(20)
declare @conversationhandle uniqueidentifier
begin
receive top(1)
@conversationhandle =conversation_handle,
@message_type_name=message_type_name,
@message=message_body
from dbo.receiverqu --> changed the name to invalid table_name
if @message_type_name='req1'
begin
insert into temp_table values(@message);
end
--end conversation @conversationhandle with cleanup;
end
end
2. Now again run the code to queue a message.
Declare @message char(10);
declare @conversationhandle uniqueidentifier
set @message='hi'
begin dialog conversation @conversationhandle
from service sendreqservice
to service 'receivereqservice'
on contract testcontract
with encryption =off;
send on conversation @conversationhandle
message type req1
(@message);
--end conversation @conversationhandle
select * from temp_table
select * from sys.conversation_endpoints
You will see that the status in the sys.conversation_endpoints is again "CONVERSING". Which is same as the successful execution but the difference is that the message is not processed, you can also check in the profiler trace that we called the stored procedure to process the message but it failed to execute as it was invalid.
1. Now correct the stored procedure:
alter procedure sp_processqueue
as
begin
Declare @message char(20)
declare @message_type_name varchar(20)
declare @conversationhandle uniqueidentifier
begin
receive top(1)
@conversationhandle =conversation_handle,
@message_type_name=message_type_name,
@message=message_body
from dbo.receiverqueue -->changed the name to valid table_name
if @message_type_name='req1'
begin
insert into temp_table values(@message);
end
--end conversation @conversationhandle with cleanup;
end
end
2. Again run the below code to enqueue the message:
Declare @message char(10);
declare @conversationhandle uniqueidentifier
set @message='hi'
begin dialog conversation @conversationhandle
from service sendreqservice
to service 'receivereqservice'
on contract testcontract
with encryption =off;
send on conversation @conversationhandle
message type req1
(@message);
If you see in the profiler trace, there will be no call to the stored procedure at target. Hence the message don’t get processed as its still considered as invalid until we run manually at least once.
To sort out the problem, run the stored procedure manually:
exec sp_processqueue
After this you will find the messages being processed, run the below query to confirm the same.
select * from temp_table
Resolution
==========
1. You will need to run the target procedure manually.
It will deliver the messages to the target and release the space from the OBJECTSTORE_SERVICE_BROKER objectstore.
2. But to clear the messages from the service_endpoints view you will have to change the procedure code to end the conversations (with cleanup).
By:
Harsh Chawla
SE, Microsoft SQL Server
Reviewed by
Nickson Dicson
TL, Microsoft SQL Server
Mukesh Nanda
TL, Microsoft SQL Server
Amit Banerjee
SEE, Microsoft SQL Server