April 1, 2010 at 9:36 am
Hi,
In one of our processes, we need to push 40 Million records daily to an Oracle Server from one of our SQL Server. The size of data is 12 Gigs. The SQL Server where data resides is independent of the SSIS Server.
We get the following error while running the process.
"Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 16 buffers were considered and 16 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."
The error indicates clearly that, the server has lower memory than it is required for this operation. Increasing the memory of the server for this one process. What else can be tried, I have already assigned directories for properties BuffertempStoragePath and BLOBTempStoragePath. The dataflow task propert RunInOptimizedMode is True. Increased the DefaultBufferSize to the Max allowed value.
The issue in our SSIS Server. The Server has 3 Gigs of RAM and enough disk space.
The major problem I have here is, I do not own the data and there is no way I can break this in smaller chunks and move them in pieces. I only need to move the data at a single stretch.
Any help is appreciated..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
April 2, 2010 at 6:14 am
Still waiting for any expert opinions..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
April 2, 2010 at 6:39 am
What does your SSIS package do?
Does it do a simply reading of the data residing in the SQL Server and writing it to the Oracle Server, or are there other operations performed?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 2, 2010 at 7:10 am
It does not do any transformations, all it does is to move these 40 odd Million records from one table in SQL Server to a table in Oracle. Since the destination is Oracle, I do not have fast load option..
Thanks for your time.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
April 2, 2010 at 7:35 am
What is the value of Maximum Insert Commit Size? If it is equal to 0, SSIS will try to insert all the rows in one single transaction.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 2, 2010 at 7:57 am
Hi thanks again for that.
I am using OLE DB Destination and I do not have table or fast load option for data access mode. The property you mentioned is only available if I choose Table or View - Fast load option..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
April 2, 2010 at 8:12 am
Bru Medishetty (4/2/2010)
Hi thanks again for that.I am using OLE DB Destination and I do not have table or fast load option for data access mode. The property you mentioned is only available if I choose Table or View - Fast load option..
You have two options create SSIS package and automate it so it runs for a long time to move your data, the other option is create an ADO.NET Dataset which leaves your data in memory and run your T-SQL code from the command object. The former is data automation and the later is a small application.
Kind regards,
Gift Peddie
April 2, 2010 at 8:29 am
Gift Peddie (4/2/2010)
You have two options create SSIS package and automate it so it runs for a long time to move your data, the other option is create an ADO.NET Dataset which leaves your data in memory and run your T-SQL code from the command object. The former is data automation and the later is a small application.
Thanks Gift Peddie !
I am not sure if you have seen the OP, I am having an SSIS package already and it does no good when it is automated (scheduled as a SQL Agent job). Either I run it manually (for test purpose) or let it run as a job, the max it runs is for 10-15 minutes (during which time it slowly creeps up the memory utilization on the Server) and once it runs short of memory, the error mentioned in the original post is thrown and the data movement is broken.
Regarding your 2nd option, even if its going to store the data in memory, I would not be able to add that amount of Memory on this box for this process, I would have to try another way out.
Note: If I had enough memory, my SSIS package would have completed without errors. I cannot add another 10-12 Gigs of Memory on the box for this purpose.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
April 2, 2010 at 8:43 am
Bru Medishetty (4/2/2010)
Hi thanks again for that.I am using OLE DB Destination and I do not have table or fast load option for data access mode. The property you mentioned is only available if I choose Table or View - Fast load option..
Whoops, I didn't realize that.
Is it a possibity to add a row number in your initial select statement? Then you can put the data flow task in a for loop and select each time a subset of the rows.
Something in the like of:
select * from
(select columnA, ..., columnB, rownumber() over (order by myPK asc) AS RowNumber from myTable)
where RowNumber >= i*1000000 AND RowNumber < (i+1)*1000000
where i is your iterator from 0 to 40.
That way, you will only process one million rows at the time.
Just a wild guess, right before the weekend, so don't shoot me if it doesn't make any sense 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 2, 2010 at 8:45 am
Thanks for that, even a wild guess is a better than no guess.
I shall try that when I return to work on Monday..
Thanks for your time.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
April 2, 2010 at 8:50 am
Bru,
If you cannot use a dataset then you may have to use SQL Server Agent in another server try running it when the Agent is idle because when we ran a job for five hours that is the only task for that Agent. Microsoft have also documented this that Agent work load affects if SSIS job fails.
Kind regards,
Gift Peddie
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply