March 25, 2012 at 11:23 pm
We have a ssis package that failed last night.
I looked at the log...as below: I don't know if the onWarning caused the message below that says low on virtual memory , and hence cause the package to fail.
Any clue from below why the package failed, how can I fix it?
OnWarning,Sqlserver1,mydomain\sqlsvs,df staging data,{AFE86459-7175-4DD9-8EC1-C531C992F052},{9DB42827-6128-4F9A-99D3-18E77C3B0521},3/25/2012 7:31:19 PM,3/25/2012 7:31:19 PM,-2145349404,0x,The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.
The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 98 buffers were considered and 94 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.
Above message repeat itself several times....
OnError,Sqlserver1,mydomain\sqlsvs,df staging data,{AFE86459-7175-4DD9-8EC1-C531C992F052},{9DB42827-6128-4F9A-99D3-18E77C3B0521},3/25/2012 7:31:24 PM,3/25/2012 7:31:24 PM,-1073450974,0x,SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "dfSr process" (1954) failed with error code 0x80004005. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
March 25, 2012 at 11:54 pm
I don't think the warning failed your package, but the fact that you load duplicates in your lookup can easily be avoided by writing a decent query.
Regarding the memory: either design your package in such a way that it can cope with the huge load, or add more memory to the server.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 26, 2012 at 10:53 am
Thanks, the message:
The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 98 buffers were considered and 94 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.
Does it mean it runs low in Virtual memory or RAM?
Thanks
March 28, 2012 at 12:13 am
It certainly hasn't have enough RAM. But it can be both.
What are you doing in the package?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 28, 2012 at 12:20 am
check the drive on which your ssis is installed and path of your temp and cache folders.
try to optimize the process use minimum sorting operations
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
March 29, 2012 at 10:02 am
The server we have is 32 bits and only 2 GB RAM, today we extended to 4 GB.
We will see how the job runs.
Do I need to set the SQL server Max memory? even we put 4 GB Memory sticks in the server, if I go to computer -property- it shows 3.5 GB.
In this case, how much space should I setup for SQL server, and how much left for OS in case to avoid the SSIS failure?
Thanks
March 30, 2012 at 4:54 am
sqlfriends (3/29/2012)Do I need to set the SQL server Max memory? even we put 4 GB Memory sticks in the server, if I go to computer -property- it shows 3.5 GB.
That's normal--a 32-bit operating system can't access more than 4Gb of RAM, and some of that is reserved for things like graphics card memory, so you'll never quite have the full 4Gb available. As for SQL max memory, it's generally a good idea to set that in order to avoid RAM starvation for the OS; with 3.5Gb available I'd be thinking of setting SQL server to use max 2.5Gb.
March 30, 2012 at 5:59 am
I would start by limiting the amount of data in your lookup transformations. Check your queries and make sure that they are only returning the columns you need for the lookup and that they are using the correct datatype. You'll also need to address the duplicates.
If after this you are still unable to lower the memory footprint, you might want to look at loading your lookup queries into a cache transform and then using the cache transform to feed your lookup transforms.
March 30, 2012 at 8:20 am
Please post the results from your instance:
SELECT SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
CAST(physical_memory_in_bytes / (1024.0 * 1024.0 * 1024.0) AS DECIMAL(20, 2)) AS physical_memory_in_GB,
CAST(virtual_memory_in_bytes / (1024.0 * 1024 * 1024) AS DECIMAL(20, 2)) AS VAS_GB,
CAST((bpool_committed * 8) / (1024.0 * 1024.0) AS DECIMAL(20, 2)) AS buffer_pool_committed_memory_in_GB,
CAST((bpool_commit_target * 8) / (1024.0 * 1024.0) AS DECIMAL(20, 2)) AS buffer_pool_target_memory_in_GB,
(
SELECT CAST(CAST(value_in_use AS INT) / 1024.0 AS DECIMAL(20, 2))
FROM sys.configurations
WHERE name = 'min server memory (MB)'
) AS [min server memory (GB)],
(
SELECT CAST(CAST(value_in_use AS INT) / 1024.0 AS DECIMAL(20, 2))
FROM sys.configurations
WHERE name = 'max server memory (MB)'
) AS [max server memory (GB)]
FROM sys.dm_os_sys_info;
IF EXISTS ( SELECT *
FROM sys.configurations
WHERE name = 'xp_cmdshell'
AND value_in_use = 1 )
EXEC sys.xp_cmdshell
'systeminfo | find "OS Name:"';
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 30, 2012 at 9:53 am
Attached is the result
March 30, 2012 at 9:56 am
sqlfriends (3/30/2012)
Attached is the result
Please attach the results as a text file, or image screenshot. Opening Office docs from the web is against policy. Thanks.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 30, 2012 at 9:57 am
PS posting as text directly onto forum is fine too
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 30, 2012 at 10:02 am
EditionProductVersionProductLevelphysical_memory_in_GBVAS_GBbuffer_pool_committed_memory_in_GBbuffer_pool_target_memory_in_GBmin server memory (GB)max server memory (GB)
Standard Edition9.00.3068.00SP23.502.001.551.550.012097152.00
output
OS Name: Microsoft(R) Windows(R) Server 2003, Standard Edition
NULL
Text seems out of alignment, an image file is in attachemnt
March 30, 2012 at 10:22 am
OK, 32-bit OS without the 3GB switch or AWE enabled. Your SQL Server is using as much RAM as it can get, ~1.6GB, for how it is configured. This means that SSIS only has ~400MB of RAM to work in. By default applications on your server can only use a total of 2GB of RAM, meaning the ~400MB leftover is shared with all other user-applications including the SQL Server services themselves.
Here is an option for you:
1. Check the available MBs on your system over the course of a normal work cycle (e.g. week, month, day, etc.) to get an idea of how much RAM is left unused during peak usage of your server. You can do this using the Performance Monitor counter called "Memory : Available MBytes".
2a. Add the 3GB switch to your boot.ini to add an additional 1GB to the user-mode VAS. This will in effect add 1GB of RAM available for use by SQL Server and SSIS. /3GB
2b. If the results from Step 1 show you have less than 1.4GB of RAM available during peak usage then also add the /USERVA switch to your boot.ini (alsi in the /3GB article above) and set it appropriately to allow for at least 400MB RAM free at all times.
3. Explicitly set your SQL Server max memory to 1.6GB. At present it is not allowed to use more than that, but you do not have your max memory set so once you open up the extra 1GB for SQL Server to use it will grow as much as feasible (~2.5GB) and you'll be in the same boat you are now.
EXEC sys.sp_configure
@configname = 'max server memory (MB)',
@configvalue = 1600;
This will cap your SQL Server buffer pool to the same exact level it is now.
4. Reboot your server.
By doing these things it will allow SSIS to use the additional RAM made available by adding the 3GB switch, while keeping your SQL Server buffer pool at the current level of memory use.
After making these changes continue to monitor free MBs on your system. If there is always more than 400MB of RAM, you can opt to increase the size of your buffer pool incrementally using sp_configure.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 30, 2012 at 11:00 am
Thank you, sorry it is kind of hard to understand the memory configuration.
But from what paul.knibbs said to set to max 2.5 GB, but opc.three said to set it to 1.6 GB,
I am a little confusing.
We plan to migrate this server in June for 64 bits 8 GB.
But for now we have to live with it, I just wonder if I change the server max memory will help or not.
Also if that's OK not to reboot the server after changing the memory setting?
The server hosts an important application and runs both db engine and ssis on it.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply