Solved

SSIS memory failure

Posted on 2011-02-10
3
1,042 Views
Last Modified: 2012-05-11
Hi,

I have an SSIS package that runs fine in a LIVE environment (which is a well specified SQL Server cluster). I need to run the same package in the UAT environment but I'm thwarted by a memory error when it gets to a certain point in a series of data flows and SQL updates:
"There is insufficient memory available in the buffer pool".

The UAT environment is a single server and is poorly specified, particularly in respect to RAM, which is just 2GB. I feel that the issue will probably just disappear if someone beefs the RAM up to at least 4GB but does anyone have any ideas for any tricks or workarounds to try and give the SSIS package more resources to run to completion? I have commented on the low RAM in this server before but nothing has happened so I could be stuck for some time.

I tried restarting the SQL Server database engine and Task Manager shows about 1GB RAM available but still the memory error on the package. The SQL Server instance is configured for dynamic memory management, with no upper limit to the amount of RAM is can grab.

Thanks in advance.
0
Comment
Question by:irb56
  • 2
3 Comments
 
LVL 13

Accepted Solution

by:
geek_vj earned 500 total points
ID: 34868836
>>"There is insufficient memory available in the buffer pool"
This is a clear message that you are running out of memory. That is the amount of data which can be stored in buffer pool is not sufficient for retrieving the data using the SSIS package. So, there are two ways to overcome this situation:

1. Revisit the SSIS package and change it so that it retrives few data at a time instead of retrieving large amount of data at a single shot
2. Upgrade your RAM to the next level
0
 

Author Closing Comment

by:irb56
ID: 34880010
Thanks for your help. The SSIS package fails on the same data flow task each time and when I disable this task it runs all the way to completion. The problem data flow task features three lookup transforms between the OLE DB Source and OLE DB Destination. I am experimenting with partial cache settings on the lookup transforms but the default setting for partial cache (25MB) has a massive performance impact. I gave up after about half an hour of extremely slow progress and I'm now trying out a partial cache of 250MB (which might hit the memory error again).

I agree that the best solution is simply to have the server RAM upgraded.

Many thanks.  :-)
0
 
LVL 13

Expert Comment

by:geek_vj
ID: 34880141
You are welcome irb56!
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
TSQL previous 5 36
SQL Server stored proc 2 20
Server running out of memory - Something is consuming all the available memory 17 62
Query Syntax 17 36
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question