Cache Transform throws out-of-memory exception
I'm using a cache transform in a dataflow to reuse a large lookup ref data (around 1.5M records). However getting out-of-memory exceptions midway in the execution. I did monitor the memory usage and it was just above the 50% usage on the dev machine where I was testing this package. On the same machine, I then reverted to using the lookup cache with full cache. The lookup component did not throw any exceptions although the pre-execute took (as expected) around 15min. Is there a memory issue with the Cache Transform ? Here is the exception: Error: 0xC0047012 at Populate Cache LOOKUP_REF: A buffer failed while allocating 10479040 bytes. Error: 0xC0047011 at Populate Cache LOOKUP_REF: The system reports 60 percent memory load. There are 3740508160 bytes of physical memory with 1478172672 bytes free. There are 2147352576 bytes of virtual memory with 216399872 bytes free. The paging file has 5713952768 bytes with 2810195968 bytes free. Error: 0xC0208252 at Populate Cache LOOKUP_REF, Refresh REFERENCE_DOMAIN_VALUE_SK [37]: Unable to allocate memory for a new row for the main workspace buffer. An out-of-memory condition occurred. http://bennyaustin.wordpress.com
October 20th, 2010 9:43am

Hi, You can try to configure saving the cache to disk so you can use it in another data flow later. In addition, since you are testing the package in your dev machine with BIDS, then you may hit the 32bit memory limitation. By default, you can only use 2GB memory and then MAX is 3GB. However, in your production machine, I believe you are running package in 64 bit mode then the memory would not be a problem I think. Another possible workaround is caching the most common values: http://blogs.msdn.com/b/mattm/archive/2008/11/22/lookup-using-the-cache-connection-manager.aspx Hope this helps, Raymond Raymond Li - MSFT
Free Windows Admin Tool Kit Click here and download it now
October 20th, 2010 12:59pm

Thanks Raymond for the useful bits of info. I'm saving the cache to disk. You are probably right on the 32-bit dev env vs 64 bit prod. What is intriguing is the lookup component in full cache mode does not hit the memory limits in ths same 32-bit env compared to the cache transform. So I guess the problem still remains with cache transform.http://bennyaustin.wordpress.com
October 21st, 2010 3:38am

Hi, I don't know why this is happening. Perhaps we could troubleshoot this at a more in-depth level of support using one of the options explained here: http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone Thanks, Cathy Miller Microsoft Online Community Support
Free Windows Admin Tool Kit Click here and download it now
November 1st, 2010 2:24pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics