Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SSIS memory failure

Posted on 2011-02-10
3
Medium Priority
?
1,073 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 13

Accepted Solution

by:
geek_vj earned 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

604 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