Geek Answers Handbook

Running SSIS packets in separate memory allocations or increasing default buffer size?

I have an SSIS package that has a child package that fails. The error message is not very helpful.

Failed to add row to data stream task buffer with error code 0xC0047020

The problem is that I am running out of virtual memory to shut down.

I found a forum topic that can help solve the problem. http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/d6d52157-0270-4200-a8c2-585fa9a0eed5/

Of the proposed solutions, I'm not sure, but how:

  • increase default buffer size
  • Allocate the pacakage child file to its own memory allocation.

I run the package daily in SQL Server 2005. I worked fine until the 12th. I assume that the data file that we use to import data into the database has grown to a size that was large for the database. However, it is only 8.90 MB csv file. Import is a direct column for importing columns.

The children's problem package is step 1 and fails and the next 8 steps continue and successfully complete.

+6
sql-server sql-server-2005 ssis
source share
3 answers

How much memory is allocated to SQL Server? How much memory is allocated outside the SQL Server process space?

The reason I ask is because SSIS memory is allocated from the memToLeave memory area, which is outside the SQL Server process space.

For more information on setting the amount of memory available for the memToLeave portion of memory, see here .

For general SSIS performance tuning, see the following article.

http://technet.microsoft.com/en-gb/library/cc966529.aspx

I hope this makes sense, but feel free to drop me a line as soon as you digest the material.

Greetings

+4
source share

Random thought: memory leak?

Our prod cluster (large corporatebbuild) was SQL 2005 SP1. A memory leak, a reboot is required every 1-4 weeks.

SP2 + HF 3068: fixed

+1
source share

To change the buffer size, select the data flow task and change the properties of DefaultBufferMaxRows, MaxBufferSize. Together they determine the size of the buffer.

Please note that in the discussion that you linked, the user initially increased the buffer size (up to 100 MB), which probably caused the problem, and the recommendation was to reduce it . The default buffer size (10 MB) should usually be OK.

To run the child package in a separate process, set the ExecuteOutOfProcess property of the Execute Package task (in the main package).

But first of all: make sure that you are really working with virtual memory, not real memory, which is usually more likely. Look at the task manager in processes that consume memory on this computer.

+1
source share

More articles:


All Articles