Symptom 2
Because zero values are not valid values in some data types, data type conversion errors may occur when the OLE DB provider converts data types. Occasionally, a data type overflow may also occur. For example, a column of the
datetime data type has a "0-0-0 0:0:0" value. This invalid value will cause this type of error because the OLE DB provider cannot format the zeros into a valid date value for the SQL Native Client OLE DB Provider.
Symptom 3
Constraint violations may occur because duplicate primary key values are inserted into the data flow destination. For example, multiple zero values are inserted into a column of the
integer data type, or multiple NULL values are inserted into a column of the
varchar data type. Similarly, foreign key relationship may be violated when duplicate key values are inserted into the target table.
How to determine whether you are encountering this problem
The following indicators can be used to determine whether you are encountering this problem:
- You can monitor the Buffers spooled counter for the SQL Server:SSIS Pipelineperformance object to determine whether the data flow engine writes data buffers to disk because of a low-memory-resource notification.
- You can also examine the DTS.tmp file located in the temporary directory that is specified in the BufferTempStoragePath property of the data flow task. If you note the size of the DTS.tmp file is increasing, the data flow engine is writing data buffers to disk.
- If you want to determine whether an existing SSIS package has this problem, you can add a Multicast transformation to the data flow task in the SSIS package to direct a copy of the data to a flat file destination for easier observation.
- Another indicator of severely low available memory is that the following message is logged in the SSIS package log:
DTS_I_CANTRELIEVEPRESSURE The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 8 buffers were considered and 8 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.
How to monitor memory consumption for SSIS
Monitor the memory to measure the peak usage for various SQL Server Integration Services Runtime processes to calculate the maximum memory usage
Monitor the peak memory usage of the various SQL Server Integration Services Runtime processes, such as the DTExec.exe and DTSHost.exe processes by using the
Private Bytes counter of the
Process object in the Performance Monitor.
When the SQL Server Integration Services packages are running, find the maximum value for the
Private Bytes counter of the
Process object in the Performance Monitor. Consider the scenario where multiple packages run in parallel. In this scenario, add the maximum values of all the processes that are running to obtain the maximum memory usage.
Monitor the external processes to find the peaks and valleys
Monitor the peak memory usage of processes other than the SQL Server service and the SSIS service by using the
Private Bytes counter of the
Process object in the Performance Monitor. Look for peak usage times that may cause low memory for SQL Server Integration Services. For example, consider the peak times where multiple users use the Remote Desktop Protocol (RDP) to connect to the server, and consider the times when the backup software is running.
Monitor the SQL Server memory usage to find the peaks and valleys
If the message "Using locked pages for buffer pool." is not present in the recent Errorlog file, the
Private Bytes counter for the Sqlservr.exe process can indicate how much memory is consumed by the SQL Server services.
If the message is present in the recent Errorlog 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
Private Bytes counter for the Sqlservr.exe process to find the memory allocations outside the buffer pool (MemToLeave). The sum of the two values, the Total Server Memory (KB) value and the MemToLeave value, is a good estimation of the total SQL Server memory consumption.
The Performance Monitor and the Task Manager do not show the memory consumed by these buffer pool pages if one of the following is true:
- If the Lock Pages in Memory user right is assigned to the SQL Server service startup account.
- If the AWE memory is enabled.
Note If the
Lock Pages in Memory user right is not assigned to the SQL Server service startup account and the
AWE is not enabled, the
Private Bytes counter for the Sqlservr.exe process should indicate the memory consumed by SQL Services for the various SQL Server instances.
Monitor the minimum value of available memory in Windows
To monitor the available memory in Windows, use one of the following methods:
- The Available MBytes counter in the Performance Monitor.
- The Physical Memory (MB) – Free value on the Performance tab of the Task Manager.
Note In some operating systems, Physical Memory (MB) – Free is labeled as Physical Memory (K) – Available.