You have a Microsoft SQL Server 2008 Integration Services (SSIS 2008) package. The package contains a dataflow task. When you run the package on a server that is under a low-memory condition, an access violation occurs, and then the SSIS 2008 runtime process crashes. Additionally, you receive warnings about low memory that resemble the following in the SSIS 2008 Application log:
Event Name: OnInformation
Description: The buffer manager failed a memory allocation call for 10484760 bytes, but was unable to swap out any buffers to relieve memory pressure. 305 buffers were considered and 295 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.
Event Name: OnInformation
Description: The buffer manager has allocated 10485608 bytes, even though the memory pressure has been detected and repeated attempts to swap buffers have failed.
The various runtime processes that run SSIS 2008 packages are DTExec.exe, DTSHost.exe, or DTSDebugHost.exe. The process that runs the SSIS 2008 package is determined by the conditions that the package is under. The DTExec.exe process runs parent packages. The DTSHost.exe process runs child packages. The DtsDebugHost.exe process runs packages that are debugged by Business Intelligence Design Studio.
SSIS 2008 is 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. Then, the SSIS 2008 runtime process begins to swap out dataflow pipeline buffers into the temporary folder that is specified by the BufferTempStoragePath
In the situation that is mentioned in the “Symptoms” section, the data in a buffer is copied internally to a destination buffer by a transformation. 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.
This hotfix locks the destination buffer explicitly during a copy operation. This behavior prevents other threads from swapping out the destination buffer when the copy operation is running.
The fix for this issue was first released in Cumulative Update 6 for SQL Server 2008 Service Pack 1. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:
Cumulative update package 6 for SQL Server 2008 Service Pack 1
Because the 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. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 1 was released
Microsoft SQL Server 2008 hotfixes are created for specific SQL Server service packs. 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.
To work around this issue, resolve the low-memory condition. The amount of memory that can trigger this issue varies for different computers. Generally, this amount is between 32 MB and 64 MB. Therefore, we recommend that you configure the server to allocate more than 64 MB of available memory to SSIS 2008.
The default amount of available memory that signals a low-memory resource notification event fluctuates. This available memory ranges from 32 MB to 64 MB for every 4 gigabytes (GB). This problem is difficult to prevent because available memory fluctuates on busy production servers. Additionally, memory may decrease for various other reasons. For more information about memory resource notifications, visit the following Microsoft Developer Network (MSDN) Web site:
To reduce the frequency of this problem, use one of the following methods.Method 1
Add more physical memory to the computer. Method 2
Run the SSIS 2008 package on a computer that is not running an instance of SQL Server.Method 3
When you run the SSIS 2008 package, set the Maximum server memory
option for the SQL Server 2008 instance to a smaller value. This behavior increases available memory.Method 4
Exit applications that consume lots of memory when you run the SSIS 2008 package that contains dataflow tasks.
Run the SSIS 2008 package and the dataflow tasks in series instead of in parallel to decrease memory usage.Method 6
Use the method that is described in the "How to monitor memory consumption for SSIS" subsection of the "More information" section to troubleshoot the low-memory condition. Then, optimize memory use for SSIS 2008.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
How to monitor memory consumption for SSIS
Monitor memory to measure peak usage for various SQL Server Integration Services run-time processes to calculate maximum memory usage
Monitor the peak memory usage of the various SQL Server Integration Services (SSIS) run-time processes by using the
counter of the
object in Performance Monitor.
Examples of SSIS run-time processes include the DTExec.exe process and the DTSHost.exe process.
When the SSIS packages are running, find the maximum value for the
counter. When multiple packages are running in parallel, add together the maximum values of all the processes that are running to obtain the maximum value for memory usage.
Monitor external processes to find peaks and valleys
Monitor the peak memory usage of processes outside the SQL Server service and the SSIS service by using the
counter of the
object in Performance Monitor. Look for peak usage times that may decrease available memory for SSIS. For example, peak times are when multiple users use Remote Desktop Protocol (RDP) to connect to the server or when backup software is running.
Monitor SQL Server memory usage to find peaks and valleys
If the "Using locked pages for buffer pool" message is not in the most recent Error log file, the
counter for the Sqlservr.exe process can indicate how much memory is consumed by the SQL Server services.
If the "Using locked pages for buffer pool" message is included in the most recent Error log file, use the
Total Server Memory (KB)
counter of the
SQL Server: Memory Manager
performance object to measure the memory usage of the SQL Server buffer pool. Additionally, use the
counter for the Sqlservr.exe process to find the memory allocations outside the buffer pool (MemToLeave). The sum of the Total Server Memory (KB)
counter value and the MemToLeave value is a good estimate of the total amount of memory that SQL Server uses.
Performance Monitor and Task Manager do not display the memory that is consumed by these buffer pool pages when one of the following conditions is true:
Lock Pages in Memory user right is assigned to the SQL Server service startup account.
memory is enabled.
When one of these conditions is true, the
counter for the Sqlservr.exe process should indicate how much memory is consumed by the SQL Server services for various SQL Server instances.
Monitor the minimum value of available memory in Windows
To monitor how much memory is available in Windows, use one of the following methods:
counter in Performance Monitor to indicate how much memory is available.
Physical Memory (MB) – Free
value on the Performance tab of Task Manager.
In some operating systems,
the Physical Memory (MB) – Free
value is labeled as
the Physical Memory (K) – Available value.
For information about the Incremental Servicing Model for SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:
An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems
For more information about the naming schema for SQL Server updates, click the following article number to view the article in the Microsoft Knowledge Base:
New naming schema for Microsoft SQL Server software update packages
For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base:
Description of the standard terminology that is used to describe Microsoft software updates