SSIS Fails with buffer errors when run from a Job - SQL 2008 R2

Question

SSIS Fails with buffer errors when run from a Job - SQL 2008 R2

dmayley

Mr or Mrs. 500

Points: 570
More actions
April 29, 2015 at 4:06 pm

#300217

I have developed an incremental load in SSIS. It utilizes a Lookup component.
I designed it using Andy Leonard's concept here. http://www.sqlservercentral.com/stairway/72494/
I do have the Cache mode set to "Full cache".
The package works fine when run from my desktop. Goes through 25 tables. Very cool!!
I am working from a Virtual Machine with Win 7 x64, 16GB of RAM using VS 2008.
I run the package from my desktop at least 30 times while unit testing. Had no memory issues.
I setup a job to run the package on our server and it fails due to buffer failures in the Lookup component.
Server is Windows Server 2008 x32, 32GB of RAM.
Here is a sample of the error messages for one table:
Started: 3:47:01 PM
Error: 2015-04-29 15:51:13.90
Code: 0xC0047012
Source: Insert and Update Rows Policy_Producer
Description: A buffer failed while allocating 6225648 bytes.
End Error
Error: 2015-04-29 15:51:13.90
Code: 0xC0047011
Source: Insert and Update Rows Policy_Producer
Description: The system reports 79 percent memory load. There are 33821474816 bytes of physical memory with 6896791552 bytes free. There are 2147352576 bytes of virtual memory with 232964096 bytes free. The paging file has 67809259520 bytes with 39332220928 bytes free.
End Error
Error: 2015-04-29 15:51:13.90
Code: 0xC02020C4
Source: Insert and Update Rows Policy_Producer RMA Source [1]
Description: The attempt to add a row to the Data Flow task buffer failed with error code 0x8007000E.
End Error
Error: 2015-04-29 15:51:13.90
Code: 0xC0047038
Source: Insert and Update Rows Policy_Producer SSIS.Pipeline
Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "RMA Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
End Error
Other tables have similar messages.
I know there must be a simple explanation to this. Could it be the difference between x64 and x32?
Or, some setting that needs to adjusted to allow a job to use more memory??
Thanks in advance!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Martin Schoombee SSCoach Points: 19128 More actions · Answer 1

It could be...the 64-bit computing environment is able to address more memory and is known to use the available memory more effectively.

With that being said, there could be many other factors playing a role. You need to look at the overall available memory when running the process, as well as the other applications competing for resources on the server.

dmayley Mr or Mrs. 500 Points: 570 More actions · Answer 2

Martin,

Thanks for the feedback. There are no other applications competing for memory. The server is our development server. There is only one other developer besides me.

I just don't know enough about operating systems to make any changes.

Any suggestions for optimizing the memory usage for Win 2008 32bit OS would be appreciated.

Thanks

Don

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 3

Quick questions, is the server running SQL Server? Is AWE enabled on the server? What are the memory (min/max) config of the SQL Server? What is the output of SELECT @@VERRSION?

😎

The error message states that it is failing on allocating 5Mb of memory when 6577 Mb are free!

dmayley Mr or Mrs. 500 Points: 570 More actions · Answer 4

Eirikur Eiriksson (5/3/2015)
Quick questions, is the server running SQL Server? Is AWE enabled on the server? What are the memory (min/max) config of the SQL Server? What is the output of SELECT @@VERRSION?
😎
The error message states that it is failing on allocating 5Mb of memory when 6577 Mb are free!

Eirikur,

AWE is enabled. Memory Min. 2048, Max. 10,000.

Select @@version = "Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86)

Jun 17 2011 00:57:23

Copyright (c) Microsoft Corporation

Enterprise Edition on Windows NT 6.0 <X86> (Build 6001: Service Pack 1)"

HTH

Thanks

Martin Schoombee SSCoach Points: 19128 More actions · Answer 5

Pretty far behind on service packs.

As Eirikur suggested and you've confirmed, AWE should be enabled. You should also ensure that the "Lock Pages In Memory" permission is assigned to the service account.

You could also reduce the max memory setting for SQL Server a little. Other than that, there's not much more you could do if there are no other processes consuming memory during your process's execution. If you still encounter errors, the data set is just too large to be stored in memory and you'll have to try alternative methods to reduce that load.

dmayley Mr or Mrs. 500 Points: 570 More actions · Answer 6

Martin Schoombee (5/4/2015)
Pretty far behind on service packs.
As Eirikur suggested and you've confirmed, AWE should be enabled. You should also ensure that the "Lock Pages In Memory" permission is assigned to the service account.
You could also reduce the max memory setting for SQL Server a little. Other than that, there's not much more you could do if there are no other processes consuming memory during your process's execution. If you still encounter errors, the data set is just too large to be stored in memory and you'll have to try alternative methods to reduce that load.

Martin, thanks for your reply.

I will suggest that the server be updated with the latest service packs. (I do not have control over the servers)

I believe that the ultimate solution is moving to 64bit OS. Until that can be schedule and setup, I began splitting the lookup into 2 lookups. I added a where clause to the queries on my larger tables to split the rows in the set in half. This seems to be working at the moment. However, it has increased the runtime from 30 minutes to 2 hours. It is still better than using the Partial Cache option. That option ran ALL DAY regardless of the amount of memory I set.

All the response to my question were helpful, but I am marking your response as the solution. The only workable approach was to reduce the data load. (Split the lookup into two)

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 7

dmayley (5/4/2015)
Eirikur Eiriksson (5/3/2015)
Quick questions, is the server running SQL Server? Is AWE enabled on the server? What are the memory (min/max) config of the SQL Server? What is the output of SELECT @@VERRSION?
😎
The error message states that it is failing on allocating 5Mb of memory when 6577 Mb are free!
Eirikur,
AWE is enabled. Memory Min. 2048, Max. 10,000.
Select @@version = "Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86)
Jun 17 2011 00:57:23
Copyright (c) Microsoft Corporation
Enterprise Edition on Windows NT 6.0 <X86> (Build 6001: Service Pack 1)"
HTH
Thanks

You should press for an update to SP3, many issues both security and functional have been fixed since your sql server's build.

😎

Suggest you use PerfMon to check if the OS and the SSIS are pressurized during the package runs, you might want to adjust the MaxMem as Martin suggested.