none
A buffer failed while allocating 10485088 bytes. - Error RRS feed

  • Question

  • Have you seen this error?

    A buffer failed while allocating 10485088 bytes.  I understand it's a memory problem with many lookups in full cache mode.

    Is there a setting in SQL 2008r2 which could help eliminate this issue?

    Thanks


    gs
    Friday, September 2, 2011 3:57 PM

Answers

  • It's not a SQL Server problem - it's a package architecture problem.  You're asking SSIS to retain too much in memory.  SSIS can't reduce the amount of memory you've asked it to use - you have to ask it to use less.  You can use some of the techniques that are in the thread BIRocks mentioned. 

    Mucking with buffer sizes won't help you at all.

    You're probably better off attempting to not use full caches on all those lookups.  Cascading lookups might help you out, where you first try to hit a pregenerated cache (using the Cache Transformation) of "most used" cache values, then send no matches to an uncached lookup.  (See the SSIS Team Blog series on Lookups.)


    Todd McDermid's Blog Talk to me now on
    • Marked as answer by Eileen Zhao Wednesday, September 14, 2011 9:26 AM
    Thursday, September 8, 2011 3:59 PM
    Moderator

All replies

    • Edited by BIRocks Friday, September 2, 2011 4:11 PM
    • Proposed as answer by SarahLiu Wednesday, May 29, 2013 1:40 PM
    Friday, September 2, 2011 4:11 PM
  • Hi gans07,

    In your Data Flow, try changing the DefaultBufferSize and DefaultBufferMaxRows properties to something smaller. For more information about it, please see the following article,

    Improving the Performance of the Data Flow: http://msdn.microsoft.com/en-us/library/ms141031.aspx

    Thanks,
    Eileen 
    Thursday, September 8, 2011 7:17 AM
  • It's not a SQL Server problem - it's a package architecture problem.  You're asking SSIS to retain too much in memory.  SSIS can't reduce the amount of memory you've asked it to use - you have to ask it to use less.  You can use some of the techniques that are in the thread BIRocks mentioned. 

    Mucking with buffer sizes won't help you at all.

    You're probably better off attempting to not use full caches on all those lookups.  Cascading lookups might help you out, where you first try to hit a pregenerated cache (using the Cache Transformation) of "most used" cache values, then send no matches to an uncached lookup.  (See the SSIS Team Blog series on Lookups.)


    Todd McDermid's Blog Talk to me now on
    • Marked as answer by Eileen Zhao Wednesday, September 14, 2011 9:26 AM
    Thursday, September 8, 2011 3:59 PM
    Moderator
  • Ive had the same problem, but it happen just when I run the package from the MSDB, when I run from the solution I dont have this problem. Does someone knows why?

    Fabiana

    Thursday, April 9, 2015 5:28 PM
  • Same error Fabiana, just want to know why it works on the solution and fails on Management Studio.

    Do you solved it?

    Thanks in advance,

    Homero Merino

    Thursday, August 20, 2015 5:47 PM
  • was anyone able to solve it? I have some deadlines and I am struggling to fix this. I in fact set the DefaultBufferSize to the minimum value = 104857600 and I still keep getting this error. Any help would be appreciated.
    Wednesday, April 15, 2020 1:41 AM