SQL Server encounters memory pressure and generate 701 Error due to incorrect configuration of Service Broker

SQL Server encounters memory pressure and generate 701 Error due to incorrect configuration of Service Broker

Rate This
  • Comments 2

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

Leave a Comment
  • Please add 1 and 4 and type the answer here:
  • Post
  • got very use ful and apt information

  • Thanks that was informative about service broker

Page 1 of 1 (2 items)