June 6, 2011 at 10:32 am
Hello all,
I have a file that is 8 GB...
Using SSIS, we tried creating a package that would load it and it was doing fine, but we opened up Task Manager to see how the memory was getting handled and it was about to blow...So we stopped the package and the developer wrote some code to break up that huge file into smaller files...So setting up the SSIS package again, I started with the first file, and my memory was at 2.46 GB...It loaded the first file and I checked the memory and it was at 3.83...So it used 1.37 GB of memory just loading that first file...Which I though was ok b/c I thought it would clear out after the package finished running...So I moved on to the 2nd smaller file and when I started to load it, the memory was at 3.83 GB and after the package finished, it was at 5.44...So the memory is not clearing out? Is there a way I can clear this without having to reboot my machine after every file load? B/c if I keep going loading each file, the memory will eventually crash? What are the best suggestions for handling this?
Thanks in advance!
June 6, 2011 at 2:06 pm
I believe what you are seeing is expected. SQL Server will utilize as much memory as you allow it.
What SQL Server does is loads data into the buffer cache (memory) then writes it to disk. It does not flush the data from the cache unless it needs that memory.
If you have not set the max memory setting for SQL Server and this is an x64 machine - it will take all of the memory available on the system.
Since you are also running SSIS on this system, I would recommend setting the max memory for SQL Server to no more than 5GB to allow the other 3GB to be used by SSIS and the OS.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 6, 2011 at 2:26 pm
If you are really concerend you can do a DBCC DropCleanBuffers, but remember this will also affect performance as it flushes all currently "clean" buffers from the buffer pool, where "clean buffers" are buffers that have been written to disk.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply