Seeing connection time out error due to "max pool size was reached" when using Microsoft.Data.SqlClient package

Question

Seeing connection time out error due to "max pool size was reached" when using Microsoft.Data.SqlClient package

HR 0

Recently we have upgraded our nuget packages from "System.Data.SqlClient" to "Microsoft.Data.SqlClient" then started seeing below error more often in local when connecting to local DB. We haven't seen this error previously and thus I am wondering if its related to nuget package upgrade. The project is on TargetFramework "net472".

We are appropriately closing all the connections with the help of using statement and have never seen this before when we were on "System.Data.SqlClient" package.

Error Msg:
System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.. Aborting test execution.

2 answers

Your answer

Answer 1

Greg Low 1,980 Microsoft Regional Director

Have you tried setting the MaxPoolSize=XXX in your connection string? It defaults to 100 but you can change it.

HR 0 Reputation points

2024-02-09T16:16:31.5566667+00:00

yes I have tried that already and also I have tried increasing the time out as well to 30 secs which seems to help. Are there any issues with "Microsoft.Data.SqlClient" which could be causing this error?

Answer 2

Olaf Helper 47,441

This may have occurred because all pooled connections were in use and max pool size was reached.

Often cause is when you don't cleanly dispose manged object like SqlConnection, so that the connection gets back to the pool for beeing resued.

HR 0 Reputation points

2024-02-09T16:18:00.0133333+00:00

I have checked the code and we are properly closing the connections and its the same code which worked with "System.Data.SqlClient" package previously. We didn't make any changes recently.

Erland Sommarskog 121.9K MVP Volunteer Moderator

Closing is not enough. You need to dispose the connection explicitly. Else you will have to wait for garbage collection to set it, and you would be relying on chance. Best practice is to use the using clause

       using (SqlConnection connection = new SqlConnection(constring))
        {
            connection.Open();
            // Create a LINQ DataContext
            DataContext dataContext = new DataContext(connection);
            // Define your LINQ query
            var query = from c in dataContext.GetTable<YourTable>()
                        where c.SomeColumn == "SomeValue"
                        select c;
            // Execute the LINQ query and process the results
            foreach (var result in query)
            {
                // Process the result here
                SqlContext.Pipe.Send(result.ToString());
            }
        }
    }

This results in an explicit close and dispose when the block exists.

Greg Low 1,980 Reputation points Microsoft Regional Director

2024-02-10T02:56:38.3766667+00:00

Hi Erland, I don't think he wants to dispose it. He just wants to return it to the pool, which Close() should do. But that said, I'd always use the "using" pattern with connections anyway.

It is interesting that the updated library isn't working when the previous one is though. That's why I think it might be interesting to increase the pool size to see the outcome.
HR 0 Reputation points

2024-02-12T17:30:52.0833333+00:00

Yes we have "using" pattern everywhere with the connection and that is what I meant with closing.

So increasing the max pool size also didn't help. But when I increased the connection time out to "30secs" then I dont see failures. So for now I am thinking to go with increase time out fix.
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2024-02-12T21:35:12.2066667+00:00

Hm, but if you have to wait more than 15 seconds to get a connection, isn't that going to hurt?
Greg Low 1,980 Reputation points Microsoft Regional Director

2024-02-12T23:28:35.89+00:00

Please check how the name for your server is being resolved. As Erland said, it's a long time to get a connection anyway.
How are you referring to the server name in the connection string?
Greg Low 1,980 Reputation points Microsoft Regional Director

2024-02-12T23:33:14.69+00:00

Wow. I really find this Q&A interface difficult. I put a comment here before and wanted to edit it but it seems to take ages for it to appear. (It did eventually appear)

Share via

Seeing connection time out error due to "max pool size was reached" when using Microsoft.Data.SqlClient package

2 answers

Your answer