Sqldataadapter fill datatable causes memory leak

Question

0.00/5 (No votes)

See more:

I have a very strange problem where when I Fetch data from SQL DB through DataAdapter the memory in the application pool increases around 250k-300k.

  private const string _getSPName = "xxxxxxxx";
    private readonly string _connectionString;//This is assigned in the constructor
    public void populate()
    {
        using (SqlConnection sqlCon = new System.Data.SqlClient.SqlConnection(_connectionString))
        {
            sqlCon.Open();
            if (sqlCon.State == ConnectionState.Open)
            {
                using (SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand(_getSPName, sqlCon) { CommandType = CommandType.StoredProcedure })
                {
                    SqlDataAdapter da = new SqlDataAdapter();
                    DataTable dt = new DataTable();
                    da.SelectCommand = sqlCmd;
                    sqlCmd.CommandTimeout = 300;
                    da.Fill(dt);

                }
            }
        }
    }

What I have tried:

I tried explicitly disposing the Adapter, DataTable and all the connection objects and also tried explicitely calling Garbage collector. But No Luck.

Posted 20-Aug-19 18:49pm

Member 11936418

Updated 21-Aug-19 3:13am

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2019-08-20T19:17:00

Memory isn't released back to the OS when it's done with in your code unless the OS specifically asks for it - which it only does when it starts to run low.
If you use a lump of memory, the memory manager checks it's free list, and if it needs extra it requests it from the OS. That remains part of the app even when you have released it back to the heap and the GC has done it's work.

This is normal, and by design: anything else would slow down the whole system.

Maciej Los · Answer 2 · 2019-08-21T01:57:00

Solution 2

In addition to OriginalGriff[^]'s solution, i'd say that usage of using statement - C# Reference | Microsoft Docs[^] provides a way to quickly and easily dispose IDisposable objects without programmer's commitment.

So, if DataTable[^] or DataAdapter[^] causes memory leak, you can use them in between using{} statement, because both objects are disposable.
For example:

C#

using (SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand(_getSPName, sqlCon) { CommandType = CommandType.StoredProcedure })
{
	sqlCmd.CommandTimeout = 300;
    using(SqlDataReader dr = sqlCmd.ExecuteReader())
	    using(DataTable dt = new DataTable())
		    dt.Load(dr);
}

Posted 21-Aug-19 1:57am

Maciej Los

Comments

Member 11936418 22-Aug-19 2:37am

I have tried all these, but didn't help. The issue is when i get data from database and populate to datatable the memory increases rapidly and this doesn't decrease.

Leumel Adert 16-Jan-23 5:05am

had faced this issue. The Mobile Device will automatically restart during execution.
I found it hard to understand because the application runs for decades without a problem.

For Temporarily Solution on my part is having the stored procedure SQL script trimmed.
that solved my current problem.

Because of the whitespace on the data returned from SP.
It consumes a lot of memory/ or encounters a leak. I'm not so sure.

If anyone has a better idea will be much appreciated.

Dave Kreskowiak · Answer 3 · 2019-08-21T03:13:00

If you're looking in Task Manager to see how much memory your app is using, it's lying to you.

It's showing you how much memory is RESERVED for your app, not how much it's actually using. You're seeing the size of the managed heap your app has immediate access to.

When objects go out of scope, the .NET Garbage Collector deallocates the object and returns the memory back to the managed heap, not to Windows.

Sqldataadapter fill datatable causes memory leak

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0