none
"There is insufficient system memory to run this query"

    Question

  • Hi,

    Wonder if anyone can help with this. We have an app which processes 100's messages a minute. When the system is down it builds up a large number of files in the inbound file locations. All locations are hosted in a shared "receivehost". When the system comes up with a large number of file in the locations, we get the following;

    The following stored procedure call failed: " { call [dbo].[bts_InsertProperty]( ?, ?, ?, ?, ?)}". SQL Server returned error string: "[DBNETLIB][ConnectionWrite (send()).]General network error. Check your network documentation.".

    and

    The following stored procedure call failed: " { call [dbo].[bts_GetDynamicStateInfo_OracleHost]( ?, ?, ?)}". SQL Server returned error string: "There is insufficient system memory to run this query.".

    It all seems to be around having the receive location on with a large number of files in it. If we diable the rec loc, reduce significantly the file count, it seems ok. It looks like points to memory stress on SQL ?

    Any ideas :) ?

    Friday, February 06, 2009 2:11 PM

Answers

  • I can't believe I didn't check the SQL service pack levels, but it's still at SP1

    http://support.microsoft.com/kb/942661

    Updating now, I hope this will cure it.
    Monday, February 09, 2009 3:22 PM

All replies

  • Is this being run on its own host instance?

    If it is not, I would try creating a seperate host instance so windows allocates an entire space of memory for only this processes use.
    Friday, February 06, 2009 5:18 PM
  • I agree with Eric for creating new host instances for receive/send/processing, you might need also some tuning to throttle down your receive location or even move your message box to an external box, it might you have to scale your hardware up! May options but you have to know exactly what could change and makes the big impact. There is a great tool created by Clint Huffman, this tool  can help you with this, it collect the performance counter values and then nicely preview the data and define the bottlenecks. Here is the tool link http://www.codeplex.com/PAL

     

    -Firas


    Please mark answers that solve your problem.
    Sunday, February 08, 2009 8:15 AM
  • Hi Eric,

    Yes, we have a receive host dedicated to inbound processing. I've upped the memory available in host settings, but I don't believe this is a Biztalk memory issue - the error actually comes from the SQL Server.

    The SQL Server is a Windows 2003 Server R2 Enterprise box with 8GB of RAM. We've enabled AWE and allocated a max of 6GB to SQL. I can appreciate this may as simple as the solution requires more RAM on the SQL box, but the error does seem a very ungraceful way of terminating - I'd expect a sudden reduction in throughput while SQL paged?

    It seems to be related to the number of files Biztalk picks up in a batch. If we drop a file in at a time (rather than leave, say 30 files in the receive location), it copes, but clearly this isn't right. I've tried changing the batching but as I understand it, reducing the batch size to 1 would just mean more threads picking up single files and processing rather than a single thread processing 20 sequentially? Although perhaps the batch transaction size is an issue?

    Regards, Jason
    Sunday, February 08, 2009 3:41 PM
  • Hi Firas,

    Thanks for the suggestion on PAL, I have used it to try and resolve the performance issues we are also getting on the SQL box.

    I've tried throttling the inbound based on spool size (message publishing db count) but this doesn't prevent the error - the error occurs as soon as the receive locations are enabled!

    Regards, Jason
    Sunday, February 08, 2009 3:58 PM
  • I can't believe I didn't check the SQL service pack levels, but it's still at SP1

    http://support.microsoft.com/kb/942661

    Updating now, I hope this will cure it.
    Monday, February 09, 2009 3:22 PM
  • That is what I love about this BizTalk forum! It could very well be a SQL Server issue!
    Monday, February 09, 2009 3:35 PM