Exception of type 'System.OutOfMemoryException' was thrown. Why?

Question

I have a dynamic query that returns around 590,000 records. It runs successfully the first time, but if I run it again, I keep getting a System.OutOfMemoryException. What are some reasons this could be happening?

The error is happening here:

  public static DataSet GetDataSet(string databaseName,string
                                   storedProcedureName,params object[] parameters)
    {
        //Creates blank dataset
        DataSet ds = null;

        try
        {
            //Creates database
            Database db = DatabaseFactory.CreateDatabase(databaseName);
            //Creates command to execute
            DbCommand dbCommand = db.GetStoredProcCommand(storedProcedureName);
            dbCommand.CommandTimeout = COMMAND_TIMEOUT;
            //Returns the list of SQL parameters associated with that stored proecdure
            db.DiscoverParameters(dbCommand);

            int i = 1;
            //Loop through the list of parameters and set the values
            foreach (object parameter in parameters)
            {
                dbCommand.Parameters[i++].Value = parameter;
            }
            //Retrieve dataset and set to ds
            ds = db.ExecuteDataSet(dbCommand);
        }
            //Check for exceptions
        catch (SqlException sqle)
        {
            throw sqle;
        }
        catch (Exception e)
        {
            throw e; // Error is thrown here.
        }
        //Returns dataset
        return ds;
    }

Here is the code the runs on the button click:

protected void btnSearchSBIDatabase_Click(object sender, EventArgs e)
{

        LicenseSearch ls = new LicenseSearch();

        DataTable dtSearchResults = new DataTable();

        dtSearchResults = ls.Search();

        Session["dtSearchResults"] = dtSearchResults;

        Response.Redirect("~/FCCSearch/SearchResults.aspx");
        }
        else
            lblResults.Visible = true;
    }

Answer 1

It runs successfully the first time, but if I run it again, I keep getting a System.OutOfMemoryException. What are some reasons this could be happening?

Regardless of what the others have said, the error has nothing to do with forgetting to dispose your DBCommand or DBConnection, and you will not fix your error by disposing of either of them.

The error has everything to do with your dataset which contains nearly 600,000 rows of data. Apparently your dataset consumes more than 50% of the available memory on your machine. Clearly, you'll run out of memory when you return another dataset of the same size before the first one has been garbage collected. Simple as that.

You can remedy this problem in a few ways:

• Consider returning fewer records. I personally can't imagine a time when returning 600K records has ever been useful to a user. To minimize the records returned, try:

  • Limiting your query to the first 1000 records. If there are more than 1000 results returned from the query, inform the user to narrow their search results.

  • If your users really insist on seeing that much data at once, try paging the data. Remember: Google never shows you all 22 bajillion results of a search at once, it shows you 20 or so records at a time. Google probably doesn't hold all 22 bajillion results in memory at once, it probably finds its more memory efficient to requery its database to generate a new page.

• If you just need to iterate through the data and you don't need random access, try returning a datareader instead. A datareader only loads one record into memory at a time.

If none of those are an option, then you need to force .NET to free up the memory used by the dataset before calling your method using one of these methods:

• Remove all references to your old dataset. Anything holding on to a refenence of your dataset will prevent it from being reclaimed by memory.

• If you can't null all the references to your dataset, clear all of the rows from the dataset and any objects bound to those rows instead. This removes references to the datarows and allows them to be eaten by the garbage collector.

I don't believe you'll need to call GC.Collect() to force a gen cycle. Not only is it generally a bad idea to call GC.Collect(), because sufficient memory pressure will cause .NET invoke the garbage collector on its own.

Note: calling Dispose on your dataset does not free any memory, nor does it invoke the garbage collector, nor does it remove a reference to your dataset. Dispose is used to clean up unmanaged resources, but the DataSet does not have any unmanaged resources. It only implements IDispoable because it inherents from MarshalByValueComponent, so the Dispose method on the dataset is pretty much useless.

Answer 2

Perhaps you're not disposing of the previous connection/ result classes from the previous run which means their still hanging around in memory.

Answer 3

You're obviously not disposing of things.

Consider the "using" command when temporarily using objects that implement IDisposable.