sql-serverssisout-of-memory

SSIS - out of memory error again


I have cca 25 databases which I need to consolidate into 1 database. First I tried to build a ssis package which would copy all data from each table into one place but then I got error:

Information: The buffer manager failed a memory allocation call for 10485760 bytes, but was unable to swap out any buffers to relieve memory pressure. 1892 buffers were considered and 1892 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.

Then I realized this is not good idea and that I need to insert only new records and update existing ones. After that I tried this option:

enter image description here

Here's how data flow task looks like

enter image description here

In some cases data flow procceses more than million rows. BUT, I still get the same error - ran out of memory.

In task manager the situation is following:

enter image description here enter image description here

I have to note that there are 28 databases being replicated on this same server and when this package is not running sql server is still using more than 1gb of memory. I've read that it's normal, but now I'm not that sure...

I have installed hotfix for SQL Server I've found in this article: http://support.microsoft.com/kb/977190 But it doesn't help... Am I doing something wrong or this is just the way things work and I am suppose to find a workaround solution?

Thanks,
Ile


Solution

  • I found a solution and the problem was in SQL Server - it was consuming too much of memory. By default max server memory was set to 2147483647 (this is default value). Since my server has 4gb RAM, I limited this number to 1100 mb. Since then, there were no memory problems, but still, my flow tasks were very slow. The problem was in using Lookup. By default, Lookup selects everything from Lookup table - I changed this and selected only columns I need for lookup - it fastened the process several times.

    Now the whole process of consolidation takes about 1:15h.