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
Solved

SSIS memory failure

Posted on 2011-02-10
3
1,044 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

861 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