DataTable does not release memory

Question

I have a data loading process that load a big amount of data into DataTable then do some data process, but every time when the job finished the DataLoader.exe(32bit, has a 1.5G memory limit) does not release all the memory being used.

I tried 3 ways to release memory:

1. DataTable.Clear() then call DataTable.Dispose() (Release about 800 MB memory but still increase 200 MB memory every time data loading job finish, after 3 or 4 times of data loading, out of memory exception thrown because it exceeds 1.5 G memory in total)
2. Set DataTable to null (No memory released, and if choose load more data, out of memory exception thrown)
3. call DataTable.Dispose() directly (No memory released, and if choose load more data, out of memory exception thrown)

Following is the code I tried for testing(In the real program it is not called recursively, it is triggered by some directory watching logic. This code is just for testing. Sorry for the confusion.):

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace DataTable_Memory_test
{
class Program
{
    static void Main(string[] args)
    {
        try
        {
            LoadData();                
            Console.ReadKey();

        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
            Console.ReadKey();
        }
    }

    private static void LoadData()
    {
        DataTable table = new DataTable();
        table.Columns.Add("Dosage", typeof(int));
        table.Columns.Add("Drug", typeof(string));
        table.Columns.Add("Patient", typeof(string));
        table.Columns.Add("Date", typeof(DateTime));

        // Fill the data table to make it take about 1 G memory.
        for (int i = 0; i < 1677700; i++)
        {
            table.Rows.Add(25, "Indocin", "David", DateTime.Now);
            table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
            table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
            table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
            table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
        }
        Console.WriteLine("Data table load finish: please check memory.");
        Console.WriteLine("Press 0 to clear and dispose datatable, press 1 to set datatable to null, press 2 to dispose datatable directly");
        string key = Console.ReadLine();
        if (key == "0")
        {
            table.Clear();
            table.Dispose();
            Console.WriteLine("Datatable disposed, data table row count is {0}", table.Rows.Count);
            GC.Collect();   
            long lMemoryMB = GC.GetTotalMemory(true/* true = Collect garbage before measuring */) / 1024 / 1024; // memory in megabytes
            Console.WriteLine(lMemoryMB);

        }
        else if (key == "1")
        {
            table = null;
            GC.Collect();
            long lMemoryMB = GC.GetTotalMemory(true/* true = Collect garbage before measuring */) / 1024 / 1024; // memory in megabytes
            Console.WriteLine(lMemoryMB);
        }
        else if (key == "2")
        {
            table.Dispose();
            GC.Collect();
            long lMemoryMB = GC.GetTotalMemory(true/* true = Collect garbage before measuring */) / 1024 / 1024; // memory in megabytes
            Console.WriteLine(lMemoryMB);
        }
        Console.WriteLine("Job finish, please check memory");
        Console.WriteLine("Press 0 to exit, press 1 to load more data and check if throw out of memory exception");
         key = Console.ReadLine();
        if (key == "0")
        {
            Environment.Exit(0);
        }
        else if (key == "1")
        {
            LoadData();
        }
    }
  }
}

Solution 1

Eventually I found this Data table not release memory bug was caused by Oracle bulk copy. Just in case some one got the same problem. Please see following post for reference

OracleBulkCopy Memory Leak(OutOfMemory Exception)

Solution 2

Your main problem is the behavior of the Garbage Collector is different depending on if you are debugging or in release mode without a debugger present.

When in a debug build or a release build with a debugger present all objects have their lifetimes extended to the entire lifetime of the method. What this means is table can not be reclaimed by the GC until you have completed the LoadData method. This is why you keep running out of memory.

If you change your program in to release mode and run it without the debugger then as soon as you pass the last reference to the object the variable table points to in your code path the object becomes eligible for garbage collection and you get the memory freed.

The reason the GC changes it's behavior during a "debuggable situation" is think of the debugger itself as holding a reference to all variables that are in scope of the currently executing code. If it did not you would not be able to look at the value of a variable in the watch window or mousing over it. Because of that you can not "pass the last reference to the object" until the variable goes out of scope or you overwrite the variable.

See the blog posting On Garbage Collection, Scope and Object Lifetimes for more detailed information about the process.

Solution 3

If you move the part when you ask to reiterate outside of the function, the memory is freed correctly (only tested method 1(Clear and Dispose)):

static void Main(string[] args)
{
    try
    {
        string key;
        do
        {
            LoadData();
            Console.WriteLine("Job finish, please check memory");
            Console.WriteLine("Press 0 to exit, press 1 to load more data and check if throw out of memory exception");
            key = Console.ReadLine();
        } while (key == "1");
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.ToString());
        Console.ReadKey();
    }
}

Probably, the objects' memory is freed when they are out of scope

Solution 4

There's not really a way to force C# to release memory as you would with code that doesn't have memory management. It helps to understand how the .NET garbage collector works. Basically memory usage in .NET apps rises to one of three conditions which trigger a garbage collection. I describe the process in the answer to the following question:

Cleaning up variables in methods

One way to avoid the OutOfMemory exception is to utilize the MemoryFailPoint class, which allows you to set a fail point beyond which an InsufficientMemoryException is thrown, giving you an opportunity to slow down the process until another worker thread is available. I'm not sure if this is something you'd want to try, but it's available to you:

https://msdn.microsoft.com/en-us/library/system.runtime.memoryfailpoint%28v=vs.100%29.aspx?f=255&MSPPError=-2147217396

View more solutions

Comments

[Scott Chamberlain]

Running in release mode without the debugger would have fixed this, the OP tired that and still got the OOM error.

[mhan0125]

I think codroipo's way is right, it frees the memory after each run.

[Panagiotis Kanavos]

@ScottChamberlain by keeping the reference to the DataTable the OP is preventing the GC from collecting any leftover data, eg change tracking data. Running in Release mode won't change this. The simple solution is what codroipo proposes - don't reuse the variable, null it or better, move it inside a different method so that it can be collected when it goes out of scope

[Panagiotis Kanavos]

A simpler fix is to simply null the variable and create a new instance. By keeping the same object alive, internal structures can never be released.

[maniak1982]

The user has seen the OutOfMemory error when he does this. It's a good idea to use objects within narrow scope, but simply setting it to null isn't enough to trigger garbage collection, as I've noted above.

[mhan0125]

@PanagiotisKanavos Makes sense to me.

[Scott Chamberlain]

@PanagiotisKanavos If you are not running with the debugger attached once the last useage of table has been passed then the object is able to be garbage collected, when you have a debugger attached lifetimes of all objects are extended to the end of the method (due to him using a recursive testing method the variables will not be released till he exits the program)

[Panagiotis Kanavos]

Didn't say it does. NOT setting to null though prevents garbage collection. Anyway, I just noticed, the OP is calling LoadData recursively, essentially never releasing old DataTables.

[Panagiotis Kanavos]

@ScottChamberlain it doesn't matter here at all - release or debug, the DataTable variables are never cleared, so the instances can never be garbage collected. If you notice the OP is calling LoadData recursively so a new DataTable is always created while the old one remains alive

[maniak1982]

Ah, that makes sense. A good thing to remember as he's refactoring.

[Scott Chamberlain]

@PanagiotisKanavos I am not talking about release mode or debug mode, I am talking about how the JITer handles the lifetimes of objects when jitted with the debugger attahched vs without the debugger attached. Please read On Garbage Collection, Scope and Object Lifetimes It explains the difference in detail.

[mhan0125]

In the real program it is not called recursively, it is triggered by some directory watching logic. This code is just for testing. Sorry for the sorry for the confusion.

[Scott Chamberlain]

@PanagiotisKanavos Run this program in and out of the debugger in debug mode and release mode. In Debug-Debugger and Release-Debugger modes it prints "Yes", in Release-NoDebugger it will print "No", and in Debug-NoDebugger it may print "Yes" or "No" depending on your CLR version I believe.

[Panagiotis Kanavos]

@ScottChamberlain you are missing the point - the DataTables are alive, which is why they won't be garbage-collected, with or without a debugger attached

[Scott Chamberlain]

@PanagiotisKanavos No, you are missing the point, the rules for what is alive and what is not change if you do not have a debugger attached and are in release mode. If you build the OP's program in release mode, in x86 mode, and run it without the debugger I could not get it to leek the memory, it always went back down to 11 MB after every GC.Collect(). Running it in the debugger or running it in Debug mode made it blow up just like the OP sees.

[mhan0125]

@ScottChamberlain Exactly, you are right. Sorry that I misunderstood you earlier. If you summarize your comment, I will take it as answer.