SSIS: A buffer failed while allocating 10485104 bytes; The system reports 98 percent memory loaded

  • hemani2000

    SSC-Addicted

    Points: 427

    I have a SSIS package and it has Fuzzy Lookup with reference table with almost 2 million records(The reference table will keep growing). When I run the SSIS package it gives me the following error message:

    A buffer failed while allocating 10485104 bytes; The system reports 98 percent memory loaded

    Any help will be appreciated.

  • hemani2000

    SSC-Addicted

    Points: 427

    Still waiting for a response.

  • Phil Parkin

    SSC Guru

    Points: 244656

    Perhaps the lack of response is the result of a lack of question.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • drew.allen

    SSC Guru

    Points: 76739

    I was able to get around a similar problem with fuzzy grouping by partitioning on one of the grouping fields using a ForEach ADO Enumerator. This worked, because I was grouping on three or four fields, but I wanted that particular field to match exactly.

    Since you haven't given specifics about your source data and your lookup data, I can't say whether a similar approach will work for your fuzzy lookup.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • hemani2000

    SSC-Addicted

    Points: 427

    I have a fuzzy lookup with ID filed as exact match and LastName, FirstName as fuzzy Match.

  • kramaswamy

    SSCoach

    Points: 18135

    Could it be that your system doesn't have enough memory to handle the task you're asking of it?

    I know that when you're using a standard lookup, it will crash if you run out of memory space when using partial lookups.

  • hemani2000

    SSC-Addicted

    Points: 427

    For that I am using 'BufferTempStoragePath' so that if the memory gets full it will use the BufferTempStoragePath location.

  • aco_koyot

    SSC Enthusiast

    Points: 156

    I had a similar problem. My buffer could not allocate sufficient amount of memory and execution was failing. The package structure may be different than yours, but I had a massive data flow that took in an XML and loaded it into a wide table.

    I've tried braking the data flow into multiple ones, changing the DefaultBufferSize, limiting SQL Server memory allocation, and it turned out to be the DefaultBufferMaxRows property on the data flow. I believe that default is set to 10,000 and I changed it to 1,000.

  • rohitkocharda

    Mr or Mrs. 500

    Points: 522

    I am having the same issue. I changed the default buffersize to 104857600 which isthe minimum value and chnaged the DefaultBfufferMaxRows to 400 but I keep getitng the same error. I have plenty of memory available on my machine.

     

    I get error:

     

    Error: The system reports 50 percent memory load. There are 34359332864 bytes of physical memory with 16931729408 bytes free. There are 4294836224 bytes of virtual memory with 109977600 bytes free. The paging file has 68716720128 bytes with 56165310464 bytes free.

    Error: A buffer failed while allocating 7453916 bytes.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply