Microsoft distributes Microsoft SQL Server 2008 fixes as one downloadable file. Because the fixes are cumulative, each new release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release.
Symptoms
Consider the following scenario:
-
You run a SQL Server Integration Services (SSIS) package in SQL Server 2005 or in SQL Server 2008.
-
The SSIS package contains a data flow task.
-
You run the SSIS package on a computer that has low available memory. The low-memory condition may occur temporarily or intermittently.
-
When you run the SSIS package, data buffers are written to disk because of the low-memory condition.
In this scenario, you may experience one or more of the following symptoms:
Symptom 1
Data is damaged before the data reaches the data flow destination. You find that the data in the data flow destination becomes the following values unexpectedly.
-
"" : NULL, blank, or empty string values
-
0 : Zero value or zero value that has higher precision and scale.
-
0-0-0 0:0:0 : Dashed zero date value
Note You will not receive an error message when you experience this symptom.
Symptom 2
You receive one of the following data type conversion error messages:
Error message 1
DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".
Error message 2
DTS_E_COLUMNSTATUSERROR. There was an error with input column "ColumnName" (IDnumber) on input "OLE DB Destination Input" (IDNumber). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
Symptom 3
You receive the following constraint violation error message:
DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PrimaryKeyName'. Cannot insert duplicate key in object 'TableName'.".
Additionally, if you run the same SSIS package again when memory is available, this issue does not occur.
Cause
When a low-memory-resource notification is sent to the data flow engine, the data flow engine writes data buffers to disk temporarily. This issue occurs because data values are set to NULL or zero incorrectly when the data flow engine writes data buffers to disk. Therefore, when the data flow engine reads data from data buffers and then writes data to the data flow destination, the data flow destination will contain damaged data values. The damaged data values may also result in data type conversion errors or constraint violation errors.
Resolution
Cumulative update information
The release version of SQL Server 2008
The fix for this issue was first released in Cumulative Update 6. For more information about how to obtain this cumulative update package for SQL Server 2008, click the following article number to view the article in the Microsoft Knowledge Base:
971490 Cumulative update package 6 for SQL Server 2008Note 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:
956909 The SQL Server 2008 builds that were released after SQL Server 2008 was released
SQL Server 2008 Service Pack 1 (SP1)
Important You must install this fix if you are running SQL Server 2008 Service Pack 1 (SP1).
The fix for this issue was also released in Cumulative Update 3 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:
971491 Cumulative update package 3 for SQL Server 2008 Service Pack 1Note 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. Microsoft recommends 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:
970365 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.
SQL Server 2005 Service Pack 2 (SP2)
Important You must install this fix if you are running SQL Server 2005 Service Pack 2.
The fix for this issue was first released in Cumulative Update 15 for SQL Server 2005 Service Pack 2. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:
972510 Cumulative update package 15 for SQL Server 2005 Service Pack 2Note 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 2005 fix release. Microsoft recommends 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:
937137 The SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 2 was released
Microsoft SQL Server 2005 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2005 Service Pack 2 hotfix to an installation of SQL Server 2005 Service Pack 2. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.
SQL Server 2005 Service Pack 3 (SP3)
Important You must use this fix if you are running SQL Server 2005 Service Pack 3.
The fix for this issue was also released in Cumulative Update 5 for SQL Server 2005 Service Pack 3. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:
972511 Cumulative update package 5 for SQL Server 2005 Service Pack 3Note 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 2005 fix release. Microsoft recommends 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:
960598 The SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 3 was released
Microsoft SQL Server 2005 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2005 Service Pack 3 hotfix to an installation of SQL Server 2005 Service Pack 3. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.
Workaround
To work around this issue, resolve the low-memory condition. The minimum amount of memory that can trigger this issue varies from computer to computer. Generally, this amount is between 32 megabytes (MB) and 64 MB of available memory. Therefore, we recommend that you configure the server to allocate more than 64 MB of available memory to SSIS.
The default amount of available memory that signals a low-memory-resource notification event is approximately 32 MB per 4 gigabytes (GB) to a maximum of 64 MB per 4 GB. For more information, visit the following Microsoft Developer Network (MSDN) Web site:
http://msdn.microsoft.com/en-us/library/aa366541(VS.85).aspx
However, it is difficult to fully prevent this problem because available memory may fluctuate highly on busy production servers.Additionally, memory can decrease for various reasons within usual activities. To help 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 package on a different computer than the computer that is running the instance of SQL Server.
Method 3
If you run the SSIS package on the same computer that is running the instance of SQL Server, set the max server memory configuration option for the instance to a smaller value to increase the available memory on the computer.
Method 4
Exit stressful applications that consume lots of memory when you run SSIS Packages that contain Data Flow tasks.
Method 5
Run SSIS packages and the data flow tasks serially instead of in parallel to reduce the cumulative consumption of memory usage.
Method 6
Use the method that is described in the "How to monitor memory consumption for SSIS" subsection in the "More Information" section to troubleshoot the low-memory condition, and then optimize memory use for SSIS.
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
More Information
More details about the symptoms
Symptom 1
If you find damaged data values in the data flow destination, the damaged data values may appear as different representations depending on the data type. For example, the following data types appear as various different representations of damaged data values:
|
DT_STR = "" |
NULL, blank, or empty string values |
|
DT_I4 = 0 |
Zero value of the integer data type |
|
DT_UI1 = 0 |
Zero value of the unsigned integer data type |
|
DT_NUMERIC = 00000000000000000000000000000000000000 |
Zero value that has higher precision and scale. |
|
DT_DBTIMESTAMP = 0-0-0 0:0:0 |
Dashed zero date value |
In most scenarios, all columns and all rows have the damaged data values, or a significant part of the columns and rows have damaged data values. Additionally, the total number of rows that reach the destination usually matches the number of expected rows at the destination even when the data is damaged. This is caused by a buffer problem. A whole buffer or a set of buffers in the running data flow task may be affected. Depending on the buffer size and the data size per row, each buffer may contain hundreds of rows.
In some advanced designs, not all columns in the data flow task are affected. If data is manipulated within the data flow task by using transformations such as expressions, derived column transformation, and lookup transformation, some columns of the data may remain the same as their expected values. This is because SSIS replaces the damaged data during the data flow task with those transformed values.
In other advanced designs, some rows may be lost. Because of the flexible nature of data direction in SSIS dataflow designs, the damaged data may be routed to an unexpected destination if the unexpected data values are perceived in the conditional column by the design. When you use transformations, such as Conditional split transformations or Lookup transformations, some rows may be lost when the transformation reroutes damaged data values. For example, in Lookup transformations, if the damaged rows do not match any reference data, the rows will be discarded.
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.
References
For more information about the MainPipeClass.BufferTempStoragePath property, visit the following Microsoft Developer Network (MSDN) Web site:
http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.pipeline.wrapper.mainpipeclass.buffertempstoragepath.aspx
For more information about how to optimize the server performance by using the memory configuration options, visit the following Microsoft Developer Network (MSDN) Web site:
http://msdn.microsoft.com/en-us/library/aa178118(SQL.80).aspx
For more information about the Performance Monitor Counters, visit the following Microsoft Web site:
http://technet.microsoft.com/en-us/library/cc768048.aspx
For more information about how to use the multicast scopes, visit the following Microsoft Web site:
http://technet.microsoft.com/en-us/library/cc758554(WS.10).aspxFor more information about the Incremental Servicing Model for SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:
935897 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:
822499New 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:
824684 Description of the standard terminology that is used to describe Microsoft software updates
