Click here to Skip to main content
15,072,267 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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))
            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;


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. 
Updated 21-Aug-19 3:13am

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 21-Aug-19 7:37am
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:
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())
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.
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.

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900