Link to home
Start Free TrialLog in
Avatar of irb56
irb56

asked on

SSIS memory failure

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.
ASKER CERTIFIED SOLUTION
Avatar of geek_vj
geek_vj

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of irb56
irb56

ASKER

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.  :-)
You are welcome irb56!