April 3, 2016 at 6:54 pm
I have a data flow that has failed on a merge (left outer join) with :
15 0xC0047048 error codes
2 0xC0047012 error codes (Description: A buffer failed while allocating 10483200 bytes.)
2 identical 0xC0047011 error codes.
The details of the latter are:
Error: 2016-04-01 07:59:39.71
Code: 0xC0047011
Source: Data Flow - XXXXX
Description: The system reports 98 percent memory load.
There are 34358267904 bytes of physical memory with 557867008 bytes free.
There are 8796092891136 bytes of virtual memory with 8765501161472 bytes free.
The paging file has 33921060864 bytes with 5509120 bytes free.
End Error
It would appear that there is not enough paging file space.
However, the server is configured with no paging file.
The dataflow is configured with BufferTempStoragePath = D:\temp.
The D: drive has 111GB of freespace which greatly exceeds the approx 32GB of "paging file" space reported.
Does the paging file indicate the storage indicated by BufferTempStoragePath?
Is there a limit to the size of a file spooled to BufferTempStoragePath?
Why is virtual memory reported as such a high value?
April 6, 2016 at 2:52 am
adamfewtrell (4/3/2016)
I have a data flow that has failed on a merge (left outer join) with :15 0xC0047048 error codes
2 0xC0047012 error codes (Description: A buffer failed while allocating 10483200 bytes.)
2 identical 0xC0047011 error codes.
The details of the latter are:
Error: 2016-04-01 07:59:39.71
Code: 0xC0047011
Source: Data Flow - XXXXX
Description: The system reports 98 percent memory load.
There are 34358267904 bytes of physical memory with 557867008 bytes free.
There are 8796092891136 bytes of virtual memory with 8765501161472 bytes free.
The paging file has 33921060864 bytes with 5509120 bytes free.
End Error
It would appear that there is not enough paging file space.
However, the server is configured with no paging file.
The dataflow is configured with BufferTempStoragePath = D:\temp.
The D: drive has 111GB of freespace which greatly exceeds the approx 32GB of "paging file" space reported.
Does the paging file indicate the storage indicated by BufferTempStoragePath?
Is there a limit to the size of a file spooled to BufferTempStoragePath?
Why is virtual memory reported as such a high value?
If you're getting issues like this then it's probably best to first look at your package design rather than head down the road of configuring server memory. Have a read of this whitepaper https://technet.microsoft.com/library/Cc966529
Some general questions to ask as you look at the design would be: Am I using all of the columns stipulated or can I discard some? Can I do the join in a query in the data source instead of in the data flow? That type of thing.
April 6, 2016 at 3:45 pm
Thank you for your suggestion.
I believe the three options I have are to increase the server memory, rewrite the package, or reduce the amount of data it is processing.
Since this is happening on a test server, not the larger Production server, reducing the data is the easiest method in this particular case.
In the longer term your suggestion of rewriting the package might be required.
However, I would still like to thoroughly understand what this error message is reporting, as I think that could help inform the decision.
April 7, 2016 at 3:11 am
adamfewtrell (4/6/2016)
Thank you for your suggestion.I believe the three options I have are to increase the server memory, rewrite the package, or reduce the amount of data it is processing.
Since this is happening on a test server, not the larger Production server, reducing the data is the easiest method in this particular case.
In the longer term your suggestion of rewriting the package might be required.
However, I would still like to thoroughly understand what this error message is reporting, as I think that could help inform the decision.
It's saying that you are running out of memory. Just to be clear, I never suggested reducing the amount of data that your DFT was using in terms of rows, I was talking about columns. For example in your data source don't use "SELECT *" or use the "Table or View" drop-down option (which issues a "SELECT *"), instead write a SQL query for only the columns that you will need. Think about what else is running on the box too. Really, adding memory to the server should be a last resort after trying to tune your DFT.
April 21, 2016 at 11:54 pm
I understand your suggestion regarding columns. We got round this one-off testing problem by borrowing a server with 128GB of memory.
However, I was really trying to understand the figures the message was reporting, particularly the paging file size, and I'm still no closer to that.
April 22, 2016 at 2:00 am
adamfewtrell (4/21/2016)
I understand your suggestion regarding columns. We got round this one-off testing problem by borrowing a server with 128GB of memory.However, I was really trying to understand the figures the message was reporting, particularly the paging file size, and I'm still no closer to that.
Okay,
I'm saying that your issue is that you are running out of memory. Entire books have been written about this subject. This is an SSIS board; if you want to learn about the specifics of memory then I'd suggest reading one of these books. Remember, nobody here is sat peering over your shoulder and since you have provided very scant details regarding your actual data flow, people have to use their experience. It's my experience that in 99% of the cases where you get this sort of error it can be resolved by tuning the SSIS package in some way. I was giving a suggestion as to where to start; this too is a big subject which I provided links for you to research further. It's great that you have resolved your issue by borrowing a server, but what happens when your data grows and that runs out of memory?
Good luck 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply