The buffer manager failed a memory allocation call for 20485670 bytes, but was unable to swap out any buffers to relieve memory pressure

A typical error occurred when a simple SSIS package has been executed to export 500 rows to another SQL instance, not once atleast 3 times! More information on error text from event viewer that has been generated by the 'service;

The buffer manager failed a memory allocation call for 20485670 bytes, but was unable to swap out any buffers to relieve memory pressure. 235 buffers were considered and 195 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.

It is obvious to assume what could be the issue, not enough memory available. It is much more confusing that the error highlights "..available to the pipeline because not enough are installed, other processes were using ....", assuming the 3 executions of this package was during a low usage time. As you may be aware that SQL Server 2008 setup installs SSIS as a seperate service, which has notified that the server is under a low-memory condition. A low-memory condition occurs when only 128 megabytes (MB) to 256 MB of memory are available. Unless you have a alert mechanism for such low resources problem it is hard to identify when this is occurred and in such low memory condition SSIS service begins to swap out dataflow pipeline buffers into the temporary folder that is specified by the BufferTempStoragePath property.

A bit of background for the settings of this value, in any instance if there is a lack of memory resource i.e. Windows triggers a low memory notification event, memory overflow or memory pressure, the incoming records, except BLOBs or NON-CHAR data will be spooled to the file system by SSIS (temp directory location). Also you need to ensure that file system location is set by the BufferTempStoragePath of the data flow task, if the SQL instance has huge traffic of data import/export operations (by default its value is blank, in that case the location will be based on the of value of the TEMP/TMP system variable).

As a further information related to this error from log:

Event Name: OnInformation

Description: The buffer manager has allocated 20485670 bytes, even though the memory pressure has been detected and repeated attempts to swap buffers have failed.

BOL specifies that both BufferTempStoragePath/BLOBTempStoragePath properties from a SSIS pacakge will specify locations where the user executing the package (if the package is being executed by SQL Server Job, then SQL Server Agent service account) has access to these locations. A small example from documentation for example, the data in a buffer is copied by a Merge Join transformation. This operation occurs while the server is under a low-memory condition. However, the transformation does not recognize that a thread outside the transformation suddenly swapped out the destination buffer. Therefore, the copy operation fails and then causes the access violation.

After ruling out the low usage on server I thought of any hotfix or bug reported to Microsoft, after searching I found that a hotfix from Cumulative update package 6 for SQL Server 2008 Service Pack 1 got the fix that prevents other threads from swapping out the destination buffer when the copy operation is running.

Just a caution of word on going for such hotfix & CU release after the service pack, as you may be aware that builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release. For more information, see SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 1 was released. Also not that you must apply a SQL Server 2008 Service Pack 1 hotfix to an installation of SQL Server 2008 Service Pack 1. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.

Next on the series I will cover about how to monitor and reduce such occurrence of low memory conditions.