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();
        }
    }
  }
}

Answer 1

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.

Answer 2

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

Answer 3

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)

Answer 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