Ever dreamt off a .Net connection leak?

If you have, then possibly you’ve rather dreamt about how to avoid it rather than how to create it.
Below I will show how to create it, avoid it and why it may happen.

First of all, why is a connection leak bad? Simply because if you leak a connection (i.e. do not close and/or dispose) it will hang around
until the garbage collector deals with it. If you have many connections that needs to be dealt with by the GC then performance will be hurt.

This is explained here:

“Using ADO.NET Performance Counters
http://msdn.microsoft.com/en-us/library/ms254503(VS.80).aspx

"The number of connections that have been reclaimed through garbage collection where Close or Dispose was not called by the application.
Not explicitly closing or disposing connections hurts performance."

This also indicates on how to check for a connection leak if this is what you suspect is causing performance issues on your side.

Ok, down to business.
We all know that when using connections in .Net the recommendation is to open the connection, do the work and then close the connection.
And when you go through your code, this is exactly what you are doing. So what gives?
The most common reason I have seen is that there is an exception occurs between the Open() and Close().
If this exception is not handled properly and/or the connection is not used within a Using statement (which takes care of the close and dispose for you)
the Close will never be called and you will have the leak.

 

So, in the example below I have hardcoded in a DivideByZeroException. But say that you have an application that between Open() and Close()
is retrieving a row from a database that has a value of 0 and that is used for division. For example, if you want to calculate the number of worked
hours for an employee and that employee has 0 hours. This will then be 365/0. And you have the error.

Simply create a new C# console application, enter the following code (fix the connectionstring so it matches your server):



 

    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Press the ANY key to RUN!");
            Console.ReadKey();
            string cs = @"Data Source=<server>;Initial Catalog=<database>;Integrated Security=TRUE; Connection Timeout=3";

            for (int i = 0; i < 2000; i++)
            {
                try

                {

                    LeakConnection(cs);

                    //LeakConnectionFixed(cs);

                }

                catch (Exception se)

                {

                    Console.WriteLine(se);

                }

                // Sleep so we can attach performance monitor.

                Thread.Sleep(250);

            }

        }

 

        private static void LeakConnection(string cs)

        {

            SqlConnection con = new SqlConnection(cs);

            con.Open();

            // Could be any exception, just using DBZE for simplicity

            throw new DivideByZeroException("Fake Div.By.Zero");

            // Will not be reached.

            con.Close();

        }

 

        private static void LeakConnectionFixed(string cs)

        {

            using (SqlConnection con = new SqlConnection(cs))

            {

                con.Open();

                // Could be any exception, just using DBZE for simplicity

                throw new DivideByZeroException("Fake Div.By.Zero");

                con.Close();

            }

        }

    }

Run the application. Hit the ANY key. Start the performance monitor (start – run – “Perfmon.exe”).
Go to the “.Net Data Provider for SqlServer”, select the “NumberOfReclaimedConnections” for the LeakDemo instance.

You should have an output like so:



so you are leaking. A simple fix is to just wrap the connection in a the Using statement. Just call that method in the code above and you’ll see the difference.

So, things to take from this.
. Use the NumberOfReclaimedConnections counter for checking for leaks.
. Run your code in Using statements. Your Close() call may not be reached even if you think that it does.