SQL Server Connection Pooling

[neilkonitzer]

I am having a problem with pooled connections not releasing when the connection objects are closed in my ASP.NET application. Even after setting the Connection Lifetime property in my connection string to a value of mere seconds, the pooled connections still appear to hang on for about 5 minutes until they are automatically released by SQL.

I have reviewed messages on other forums where other developers are having the same issues. What do I need to do to have my pooled connections released within several seconds of no longer being used rather than having to wait several minutes? This is a problem in that my application will be used by hundreds or possibly thousands of concurrent users and I do not want to receive any timeout errors as a result of my maximum pool size being exceeded.

Thanks in advance!

Neil Konitzer
Freisoft

http://www.freisoft.com

 

[jfrost10]

Hmmm...How are you destroying your objects that are using the connections? Are you getting rid of all resources within your class's Finalize method?

Never dealt with pooling before, so just throwing out a guess (even though teh connection times out, maybe the object is still holding on to the reference until its totally destroyed?)

D

 

[neilkonitzer]

I've done the whole nine yards with releasing the connection object.

cn.Close()
cn = nothing

I've tested and sure enough, the object is released as it should be. It is just that the pooled connection in SQL is not getting released until ~5 minutes later.

Neil Konitzer
Freisoft

http://www.freisoft.com

 

[RichS]

Isn't connection pooling an IIS feature that is on by default? If I remember correctly, there is nothing your asp application can do about. I think you have to turn it off in IIS.

 

[chiph]

Calling the Close method doesn't actually close the connection (the pool will keep it open for a short while in case the next caller needs it). And more importantly, setting it to Nothing does not free it from memory.

Remember that .NET is a garbage-collected language. Which means that objects will stay around until the garbage collector runs. If you explicitly want to get rid of your database connection (or any other object that encapsulates un-managed resources, like file handles, GUI handles, etc) you need to call .Dispose on it before setting it to Nothing.

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first