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.
NoteThe 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.
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.
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:
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:
Add more physical memory to the computer.
Run the SSIS 2008 package on a computer that is not running an instance of SQL Server.
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.
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.
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.
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:
- Use the
counter in Performance Monitor to indicate how much memory is available.
- View the
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 more information about the naming schema for SQL Server updates, click the following article number to view the article in the Microsoft Knowledge Base:
For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base:
ID do Artigo: 977190 - Última Revisão: 07/10/2011 - Revisão: 1