The buffer manager has allocated .. bytes even though the memory pressure has been detected and repeated attempts to swap buffers have failed.
I have read other resolutions regarding this, but I am not sure if they apply. My package is straight forward. It runs in production sporadically with normal duration of under 2 minutes, but recently has taken over 40 minutes to complete. Package has informational errors when run in BIDS some of the time. Numerous instances of the following during execute: Error: Information: The buffer manager failed a memory allocation call for 10485072 bytes, but was unable to swap out any buffers to relieve memory pressure. 123 buffers were considered and 40 were locked. Either not enough memory is available to the pipeline because not enough are installed, other processes were using it, or too many buffers are locked. and Error: Information: The buffer manager has allocated 1703808 bytes, even though the memory pressure has been detected and repeated attempts to swap buffers have failed. In instances where thes errors show in the Progress tab, the Data Flow task registers a failure, but the overall fetch and insert operation that is the goal of the task succeeds. In other tests of the same package there is no issue with the buffer allocation and the package runs without warnings or errors in the progress tab. I suspect the same is happening during execution of the package in production but have no implemented logging to prove that out. Are there default settings regarding buffers that need to be adjusted based on the number of bytes reported in the error? Other jobs are not running during this package execution and it is not clear why there would be intermittent performmance issues. I can provide more specifics about the package if necessary.
May 11th, 2012 9:47am
Memory preasure is the cause I think, see this general article: http://technet.microsoft.com/en-us/library/cc966540.aspx I had a client where errors like that proliferated and it ended up calling the hardware vendor which changed the memory chips which remedied the issue. Also worth checking whether any SPs or CUs are missing.Arthur My Blog
May 11th, 2012 9:56am
It's a simple memory pressure issue. SSIS and/or other processes have simply used too much memory, and SSIS is letting you know. In some cases it will be able to swap memory to disk - which is why it'll take a lot longer to execute - and sometimes it can't, and will fail the package. Find out what else is consuming RAM on that machine during those times. Talk to me now on
May 11th, 2012 11:49am