SQL/ADOConnection Pooling- Lifetime question

  • Thread starter Thread starter john smith
  • Start date Start date
J

john smith

We know SQL connection lifetime is 60 seconds for idle connections.
If I set the lifetime of my ADO.NET connection object to 500 seconds.
And the connection sits idle in the pool for say 150 seconds. Will SQL
shuts down its side of the connection?
If yes, well, that's easy I will know that the ADO.NET is dead from the
exception thrown next time i use it.
If not? how does ADO.NET overrides the SQL side lifetime limit?
Am I making sense or am I connecting too totally different concepts?
Thank you
 
Setting the lifetime of a connection to 500 seconds means that the
connection object cannot exist for longer than 500 seconds in the connection
pool. This means that when you close a connection and it returns to the
connection pool and the creation date + 500 seconds exceeds the current time
the object will be destroyed.

I always believed that idle connections in the connection pool will not be
closed by the SQL Server but by the connection pool manager after several
minutes of being idle. To be honest I don't know the exact amount of seconds
that a connection needs to be idle in the connection pool manager before it
gets closed but I think that it exceeds the 150 seconds. Once a connection
gets closed these are marked as invalid and destroyed in the pool. You won't
get an exception when you try to open a new connection unless you have
exceeded the number of connections in the pool and you get a connection
timeout.

Please someone correct me if am wrong.

Gabriel Lozano-Morán
Software Engineer
Sogeti
 
Back
Top