CODEWITHSUNDEEP
sqlasp.netentity-frameworkowinconnection-pooling
Mando
Mando

Reputation: 11722

ADO.NET Pooled connections are unable to reuse

I'm working on an ASP.NET MVC application which use EF 6.x to work with my Azure SDL Database. Recently with an increased load app start to get into a state when it's unable to communicate with the SQL server anymore. I can see that there are 100 active connections to my database using exec sp_who and any new connection is unable to create with the following error:

System.Data.Entity.Core.EntityException: The underlying provider failed on Open. ---> System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because of all pooled connections were in use and max pool size was reached.

Most of the time app works with average active connection count from 10 to 20. And any load doesn't change this number... Event when load is high it stays at level 10-20. But in certain situations, it could just up to 100 in less than a minute without any ramp up time and this causes app state when all my requests are failing. All those 100 connection are in sleeping state and awaiting command.

The good part is I found a workaround which helped me to mitigate the issue - clear connection pool from the client side. I'm using SqlCoonection.ClearAllPools() and it instantly closing all the connections and sp_who shows me my regular 10-20 connection after that.

The bad part, I still don't know the root cause.

Just to clarify the app load is about 200-300 concurrent users, which generate 1000 requests per minute

With the great suggestion @DavidBrowne to track leaked connection with a simple pattern I was able to find leaked connections while configuring Owin engine

private void ConfigureOAuthTokenGeneration(IAppBuilder app)
{
    // here in create method I'm creating also a connection leak tracker
    app.CreatePerOwinContext(() => MyCoolDb.Create());
    ...
}

Basically with every request, Owin creates a connection and doesn't let it go and when the WebAPI load is increased I have troubles.

Could it be the real cause and I Owin smart enough to lazy create a connection when needed (using the function provided) and let it go when it was used?

Upvotes: 3

Views: 1483

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89331

It's very unlikely that this is caused by anything other than your application code leaking connections.

Here's a helper library you can use to track when a connection is leaked, and report the call site that initially opened the connection.

http://ssbwcf.codeplex.com/SourceControl/latest#SsbTransportChannel/SqlConnectionLifetimeTracker.cs

Upvotes: 1

Related Questions