SSIS package execute - Out-of-memory issue!

Question

While executing SSIS package, I got following errors,

The buffer manager failed a memory allocation call for 10484608 bytes, but was unable to swap out any buffers to relieve memory pressure. 20 buffers were considered and 20 were locked. Either not enough memory is available to the pipeline because not enough are installed, other processes were using it, or too many buffers are locked.

[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.

[DTS.Pipeline] Error: The Data Flow task failed to create a buffer to call PrimeOutput for output "XML Source 1" (91) on component "GeneralCongfigurations" (98). This error usually occurs due to an out-of-memory condition.

It happen when trying insert data to SQL table from an XML file with 'Script Component'.

How to solve it?

Those codes are meant for googling, no one recognizes them off the bat. Also, what are the SSIS log entries before the error happened? What kind of task/step happened? Are you running this package inside of visual studio? — MatthewMartin, Jun 30 '09 at 11:26

score 4 · Accepted Answer · answered Jul 05 '09 at 20:45

The message tells you SSIS is using 20 buffers, 10Mb each - about 200Mb together. It is not very big amount of memory, even on 1Gb machine you would not run out of memory.

It is likely other processes consume the rest of the memory - check the task manager. Often it is SQL Server who consumes all the memory - if you run SQL and SSIS on same machine, restrict the amount of memory SQL is allowed to consume (in SQL Server properties), leaving some memory for SSIS - I would recommend leaving at least 0.5Gb.

thank you for this answer. this just helped us solve a similar issue — Alex Gordon, Sep 19 '12 at 16:17

score 0 · Answer 2 · answered Jul 05 '09 at 20:57

0

You should also look at all of the log messages. In particular, Lookup transforms can log a lot of information about how much memory they're using. You can get some very detailed logs about memory allocation.

answered Jul 05 '09 at 20:57

John Saunders

160,644
26
247
397

1

Could you recommend where to start looking for these memory allocation logs? – Chris Pickford Feb 18 '14 at 09:34

score 0 · Answer 3 · answered Jun 21 '16 at 07:52

I faced the same issue. My XML source is of around 2MB. when I start the package it started throwing this OutOfMomory warning. My server has around 8GB RAM. So it was not memory issues . Current machine SQL server service was taking close to 6GB space. I could check this from Task Manager. As my SSIS package destination was connecting to difference database I did not want the current server SQL service to be running. So the moment I stopped this SQL SERVER service, my SSIS package executed successfully.

score 0 · Answer 4 · answered Aug 22 '18 at 19:22

@Chris Pickford

You want to use PerfMon Counters: Memory\ Availabe Bytes/MBytes Committed Byrtes SQLServer:SSIS Pipeline\ Buffer Memory Buffers in Use Buffers Spooled

Also, if you've got logging on your package, look at event User:BufferSizeTuning

score 0 · Answer 5 · answered May 03 '23 at 00:31

Reduce your batch fetching the records from the source down until the large data sizes can come through more slowly. Changed my batch from 5000, to 1000, to 500, to 100 to bring across large amount of data from Dynamics using SSIS / Kingswaysoft connection. Resolved buffer failures.

SSIS package execute - Out-of-memory issue!

5 Answers5

Linked