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:
When running commands from tasks DB interpret this as invalid to reuse available connections
Connections aren't closing, but why? Seems to closing them with using keyword. More over that is 100
AWAITING COMMANDon the DB.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;
}
}
}
usingaround the SqlCommand?SqlConnectioninstance, 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 forSqlConnectioninstances and the GC roots keeping them alive.