SSIS Buffer/Memory Failure

Question

I get this error when running SSIS package...

A buffer failed while allocating 104857600 bytes. The system reports 31 percent memory load. There are 17179328512 bytes of physical memory with 11714314240 bytes free. There are 2147352576 bytes of virtual memory with 406519808 bytes free. The paging file has 19729465344 bytes with 13419847680 bytes free.

The memory seems ok on the machine that the package is running on. Is 31% memory load considered high? This has only started to happen after migrating to a newer server. But this new server has double the memory capacity.

No new SSIS packages have been added. The SSIS packages are stored on SQL Server 2008R2 server. They are called from another server that has SSIS 2008 running.

Any idea what may be causing this?

The SSIS package is executed through a 3rd party application. We forced this application to run as 32 bit as we were getting the below error after adding a reference to System.Data.dll

How do I force the SSIS package to run as 64bit?

Answer 1

2,147,352,576 bytes is 2Gb which implies you have a 32-bit SSIS running on 32-bit Windows, so the 16Gb total memory of which 10.9 is free is fairly meaningless but is where the 31% comes from: (17,179,328,512 - 11,714,314,240) / 17,179,328,512 = 0.318.

A 32-bit process can only use 2GB (or under some configurations 3Gb) unless it is compiled LARGEADDRESSAWARE and running on a 64-bit edition of Windows. On 64-bit Windows a large-address aware process should be able to use 4Gb (see this which refers to here).

In this case the process seems to have ~386Mb available in the 2GB address space and is failing to allocate 100Mb which is less than that - one educated guess is that this could be due to fragmentation: if SSIS is wanting a single coherent block and the remaining free address space is in blocks smaller than that spread around the address space.

No new SSIS packages have been added.

The change is likely due to increases in incoming data size in that case.

Possible Solutions

If you are running a 32-bit Windows edition you could try using the /3GB switch to ask Windows to use the 3+1 address space arrangement for 32 bit processes instead of 2+2 (see https://technet.microsoft.com/en-us/library/bb124810(v=exchg.65).aspx or similar reference for your edition of Windows) - if SSIS is capable of using the larger address space this will give it a bit more room.

If you are using a 64-bit Windows install and 64-bit SQL then try to make SSIS use its 64-bit run-time. There are issues with some data access drivers here though so that may not work. Also the design-time environment is always 32-bit. See https://technet.microsoft.com/en-us/library/ms141766(v=sql.105).aspx and other documentation.

In either case you may be able to break down your ETL process into smaller packages and chain them together with Execute Package tasks. With the "ExecuteOutOfProcess" option turned on each package called should be able to use its own 2Gb address space.

You might also try tweaking the way buffers are allocated in data flow tasks using the DefaultBufferMaxRows and DefaultBufferMaxSize properties; see here, here and here.