Hi Experts,
We have one live ASP.Net application which is used by around 1000 concurrent users. In this application we have used connection pooling. There are 2 connection string defined in web.config with different name but we are calling same DB.
<add name="connection1" connectionString="Data Source=mySQLServer;Initial Catalog=myDatabase;Integrated Security=True;Connection Timeout=300;Connection Lifetime=0;Min Pool Size=0;Max Pool Size=100;Pooling=true;" />
<add name="connection2" connectionString="Data Source=mySQLServer;Initial Catalog=myDatabase;Integrated Security=True;Connection Timeout=300;Connection Lifetime=0;Min Pool Size=0;Max Pool Size=100;Pooling=true;" />
In my application we are opening an closing the connection every where like below
try{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
}
catch(Exception ex)
{
}
finally{connection.close()}
Now issue is when I am checking Database using sp_who2, I am seeing around 1000 or more than that sleeping session and for each record program name is ".Net sql server Provider". I gone through MSDN and found that if we are using connection pooling then connection keep opened till 4-8 minutes. So I am bit confused now, if I am using only 2 connection string with connection pool then why we are seeing so many sleeping connection, I am thinking it should be only 2? Can someone please help me in this situation and suggest what is correct? And how can we minimize sleeping sessions.
Also not sure whether it's an application pool(IIS server) error or application error?
Please help.
Thanks,
What I have tried:
I tried to check Application server IIS 7 as well. I've observed that as connection are increasing application is getting slow and we need to do recycle application pool.
Not sure about exact reason why sp_who2 is showing multiple sleeping session, if I am using only 2 pool then sleeping session should be 2 I think not sure though.