adapter "SQL" : 'System.OutOfMemoryException'

Question

My application is calling a stored procedure using SQL adapter. It works fine when tested with few messages.

When I drop 200 messages, each of 2 MB size I get System.OutOfMemoryException.

1. The adapter "SQL" raised an error message. Details:"Exception of type 'System.OutOfMemoryException' was thrown.".

2. There was a failure executing the response(receive) pipeline: "Microsoft.BizTalk.DefaultPipelines.XMLReceive, Microsoft.BizTalk.DefaultPipelines, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" Source: "XML disassembler" Send Port: "SQLValidateSupplyDemand" URI: "SQL://XXXXXX/" Reason: Exception of type 'System.OutOfMemoryException' was thrown.

 

I have 8GB RAM, 32 bit host instance runninig on 64 bit machine. 20 GB free space in C drive.

To avoid the memory issue,Can I set the application to consume only 5 message and process them, as any one of them is processed, consume other message? If yes..how?

I tried looking for Host Throttling, but not able to understand what exactly to be done for it. I need to resolve this issue urgently. Any help is appreciated.

Answer 1

If you want to consume 05 messages at a time then set the Batch Size of the Receive Port to 5 in BizTalk Admin Console. That's all you need to do. In your case you can set it to some higher value also, but not more than 20.

SQL adapter uses serializable transaction and dedicated database connection (from connection pool) to communicate with database. Normally the default connection pool size is 200, but COM+ (MSDTC) fails to maintain those many distributed transactions. That's the reason for the exceptions.

---

Please mark as answer if this helps you. Thanks and warm regards Ambar Ray Solution Architect - Microsoft Technologies

Answer 2

There is no option to set batch size on Receive port. I can set that on receive location, but I have got 5 receive locations.

Moreover, I want it to consume 5 messages and then wait for one of the message to complete processing and only after that consume other one. I checked with batch size 20 (default), after consuming 20 at once, 20 instances start running, and it again consumes another 20. So if i drop 200 files I see all of them processing at the same time.

Can we do Something like,

I configure for 5 instances (processes) which means 5 files will be processed concurrently at one time (Round Robin) regardless to how many files dropped into the received folder. It shall pick the next when one of the process is completed.

 

Is it possible?

Answer 3

Well for one you can make your host instance NOT be 32 bit.  You should have enough RAM in that machine to not cause a problem then. 

-Dan

---

If this answers your question, please Mark as Answer

Answer 4

The machine on which development is done is a 32 bit machine. On deployment, when assigned to 64 bit host instance, I received error saying cannot run send handler on 64 bit.

The problem is there can be huge number of files at a time, which may exceed memory capacity of the server.

We need to limit it.

Answer 5

You probably should alter the query restricting to 5 or 10 messages at a time something like SELECT TOP 10....

It's not a good idea to poll all of them, even if you got enough RAM in the server.

---

Regards, Saravana Kumar [MVP BizTalk Server] http://blogs.digitaldeposit.net/saravana/ http://www.biztalk247.com/

Answer 6

So you need to create the host / instance as 64bit on the server (uncheck the 32bit box).  This will give you more RAM.  Saravana's suggestion about using a TOP clause is critical too.  You should always do this, even if it's TOP 1000.  In order to be getting this OOM I really think you're loading messages in the DOM.  Are you using any custom pipeline components?

-Dan

---

If this answers your question, please Mark as Answer

Answer 7

Sarvana,

My stored procedure have multiple select statements and comparisions. All statements expect one record in return.

I agree to you that polling limited number of records should be kept in mind while designing a stored proc. But in my case most of the operation are of insertion rather than read.

Answer 8

I am not using any custom pipeline components.

Answer 9

When you run this query in SQL Management Studio how big is the result set?  Clearly whatever you're doing is taking up more than 1GB RAM because on a 32bit machine that's where you'll hit the wall.  You can increase the .NET setting for how much memory the process can use, but it really shouldn't matter.  How many operations does this query actually run?  Maybe the process is getting squeezed by SQL Server as well.  Is SQL on the same box as this BizTalk instance?

-Dan

---

If this answers your question, please Mark as Answer

Answer 10

Dan,

There are roughly 10 select statements which are used to check existence of data in corresponding tables.

Not more than 3 records expected in one Query result.

It runs Validation Operations :

-- Names are passed as parameters to stored procedure
-- Check presence and if not present update error log table


If not exists (Select ID from X_Table where name='XBox')
  Insert into Err_Table values ('Err in X Data')

If not exists (Select ID from Y_Table where name='DVD')
 Insert into Err_Table values ('Err in Y Data')

SQL is on remote machine.

---

Deepshikha

Answer 11

 

It seems the problem is somewhere else might be some other code e.g. orchestration is consuming too much memory. Can you check whether BizTalk is getting throttled under memory pressure when you do the operation. You can check that by looking at the performance counter "Message Delivery Throttling State" under "BizTalk:Message Agent". Check whether the throttling state goes to "4(Throttling due to process memory pressure )". Please also check the Message publishing throttling state.

 

---

Mark As Answer or Vote As Helpful if My Reply Does, Regards, -Rohit

Answer 12

Is the SQL Stored procedure on the receive port or send port?

If it is on the send port, one way I have fixed these problems is to set the order deliver flag on the SQL send port, that forces BizTalk to single thread the stored procedure calls, this also fixes issues where you are getting dead lock in SQL.

---

Bill Chesnut BizTalk Server MVP Melbourne Australia

Answer 13

Hi Deepshikha123,

1. Old SQL Adapter is not 64 bit supported. So you should use the WCF-SQL Adapter whenever possible. (Just install the BizTalk Adapter Pack 2010 and remember to install the CU1 for the BizTalk Adapter Pack 2010 to fix some known issues) - You can use this adapter on any 64 bit host!

2. Divide your application into multiple hosts (BizTalkServerReceive (x64),BizTalkServerSend (x64),BizTalkServerSQL32 (x86),BizTalkServerApplication (x64),BizTalkServerTracking (x64)) This allows you to use more memory in total.

Use the Hosts like they are named (Receive for all receive ports, Send for all 64 bit Sendports, SQL32 for 32 bit Sendports, Application for Orchestrations and Tracking only for the Option "Allow Host Tracking" - do not run anything else on this host.)

 

This should allow you to get around the OutOfMemory problem.

 

Best regards,

Leo

---

Please mark it as Answer if this answers your question.

Answer 14

Thanks Bill!

SQL Stored procedure is on solicit-response send port. I agree that Ordered Delivery will avoid deadloacks and force BizTalk to single thread the stored procedure calls, but as an ill-effect , it will slow down the process.

I think Ordered Delivery will be something like processing one message at a time. Please correct me if I am wrong.

My problem is - there are thousands of messages to be processed, I can not restrict to single thread and loose the speed.

 

---

Deepshikha

Answer 15

Thanks Leo!

I don't think I can do any migration to WCF-SQL Adapter as of now. I'll try doing what you suggested.

---

Deepshikha