0

I've got a problem with the connection pooling with continuously running program. The problem occurs when I'm doing a lot of queries (every 4 minutes 5x (querying 3 tables and saving result to one)) to DB in the Tasks.

The connection pools run out of max pool connection size. The strange thing about this that I have a hundred AWAITING COMMAND entries for that particular connection string / machine / user entries. My understanding is that AWAITING COMMAND means that this connection can be reused, but from some strange unknown reason to me when running commands from Tasks cannot reuse available connections and they just wait for no one, and after some time got error that I've reached the max pool connections size.

Assumptions so far:

  1. When running commands from tasks DB interpret this as invalid to reuse available connections

  2. Connections aren't closing, but why? Seems to closing them with using keyword. More over that is 100 AWAITING COMMAND on the DB.

  3. The handlers aren't garbage collected for some reason? But the 100 AC suggest something else.

Local DB observations

When I try to replicate this on my local SQL Server Express, this problem happens in a very awkward situation. I had to add the Thread.Sleep(600000) to simulate the situation, and eventually after that I was able to get the max pool error. In this case all connections are open, so its rather self explanatory, though.

In the local machine -> server DB scenario, I don't think so that I could have 100 connections open at a time; they rather stay open for some reason. When launching this program on the local machine -> server DB situation I don't even need to add the Thread.Sleep(600000) in order to crash the program.

All those are my assumptions based on observations. I can't think of what is causing this in my continuous running service, where I query the DB every 4 minutes.

After my full local testing I'm confused if AWAITING COMMAND means that this connection can be reused?

I forgot to mention that my initial program can run couple of days before I eventually encounter this max pool error.

Below is the program that can generate this kind of problem:

using System;
using System.Data.SqlClient;
using System.Threading;
using System.Threading.Tasks;

namespace Pooling
{
    class Program
    {
        private static int connectionIterations;
        private static string connectionString = "Data Source=localhost;Initial Catalog=localDB;Integrated Security=True";

        static void Main(string[] args)
        {
            try
            {
                Iterations();

                while(true)
                {
                    ConnectionSnowball();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw;
            }
        }

        private static void ConnectionSnowball()
        {
            Parallel.For(0, connectionIterations, i =>
            {
                try
                {
                    Console.WriteLine($"Connection id: {i}");
                    using (SqlConnection connection = new SqlConnection(connectionString))
                    {
                        SqlCommand cmd = new SqlCommand("SELECT 1 FROM test_table", connection);
                        connection.Open();
                        cmd.ExecuteNonQuery();
                        Thread.Sleep(600000);
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(e);
                    throw;
                }
            });
        }

        private static void Iterations()
        {
            connectionIterations = 200;
        }
    }
}
5
  • 3
    Seems like a missing release/dispose of resource. Have you tried a using around the SqlCommand? Commented Nov 23, 2017 at 16:18
  • It seems connectionIterations of 200 could exceed 100, depending on the query duration. Commented Nov 23, 2017 at 16:23
  • Note that you cannot tell, by looking at it from the server, if a connection is in the pool and available for use, or in use by the client but not currently serving commands. That piece of information exists only client-side -- as long as a physical connection is being used by an undisposed SqlConnection instance, it's in use; otherwise it's not. To see if there is a resource leak on the client, create a dump file and analyze the heap for SqlConnection instances and the GC roots keeping them alive. Commented Nov 23, 2017 at 17:44
  • @FrankNielsen I will try this one. Thought that this coudn't hang the SqlConnection when used without using. Funn fact though. When you remove the Paralelism, than it uses exact one connection for all time which implicates that there is no connecion leaks. When you switch to task and connections start with 15 ie than steadly and surly goes up and up till max pool size. Maybe tasks are the problem. Commented Nov 23, 2017 at 17:44
  • 2
    Another, less probable thing to check is that the same connection string is used every time when creating a new connection (as in, character-for-character identical), as that's what pooled connections are keyed on. Commented Nov 23, 2017 at 17:44

2 Answers 2

1

I debugged your code and found no connection leaks. You just have a connection pool overflow. I checked two possible solutions for you.

Disable pooling connections

private static string connectionString = "Data Source=localhost;Initial Catalog=localDB;Integrated Security=True;Pooling=False";

Increase connection pool

private static string connectionString = "Data Source=localhost;Initial Catalog=localDB;Integrated Security=True;Max Pool Size=200";

To test how the connections will increase and decrease before, during and after the ConnectionSnowball() call, you can use this SQL query

select count(1) from sys.dm_exec_sessions where database_id = DB_ID(N'localDB')

More details about connection string parameters SqlConnection.ConnectionString Property

Other possible solutions is the use of SQL jobs. For this task, this may be a more appropriate solution, since a large number of connections are very resource intensive.

Sign up to request clarification or add additional context in comments.

Comments

0

As there are no connection leaks in your code, Did you try Restarting IIS?

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.