The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers.
[DTS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 12 buffers were considered and 12 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.
I've run into this when I'm running too many memory-intensive packages running at the same time (especially when they make heavy use of OLE DB Commands).
Try running fewer packages at once.
- Proposed as answer by spowell71 Wednesday, July 06, 2011 5:35 PM
Could you describe your package? How many data flows? How many asynchrounous/blocking transforms (Sort, Aggregate, or similar)? What is the size of data being processed?
This message gets posted when there are too many buffers in memory at the same time and they cannot get swapped by the data flow engine (locked).
I was experiencing this same problem and I ended up working around the issue by monitoring how much memory my SSIS package was trying to use (using the Performance Monitor looking at "Buffer memory" for SQLServer: SSIS Pipeline). I saw that the max amount it was trying to use was 2GB which didn't seem like too much for the 8GB of RAM on this server.
SQL Server was configured to use up as much memory as it wanted (and I verified that it used up to around 7GB of memory with the "Total Server Memory" performance counter for SQLServer: Memory Manager). After I changed the max that SQL Server could use down to 4GB my SSIS package ran fine without the 'buffer manager' warnings.
I understand this is probably not an ideal long-term solution as there will be a lot of RAM wasted most of the time but it helped me get my package running!
One interesting thing I noticed was that originally the 'buffer manager' message would crash my package but after some optimizations (serializing my process by saving things to temporary tables, letting sql do sorts instead of SSIS, etc) it ended up just running EXTREMELY slowly.
Bob, thanks for your questions.
the package is very simple. The load is a small file with a few thousand records. It does not do much other than read from sourse and insert into a destination.
What I have noticed is that our server RAM memory is pretty much all used up.
We are going to migrate to a 64 bit server so we can increase the memory from 4GB to 8GB.
Thanks DeveloperGoblin for your input. It is very useful too. I will keep it in mind whe we move to a bigger server
You can find lots of info about How to adjust memory usage by using configuration options in SQL Server from below links:
Thanks Shiven:) If Answer is Helpful, Please Vote