Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite
1

On SQL Server, I'm trying to prevent connection from staying open. From here I understand that Connection Lifetime allows a connection to stay alive for a certain period of time since it was opened.

Quote: When a connection is returned to the pool, its creation time is compared with the current time...

Is there a way to do something similar, but with connections who are idle? When they return to the pool they'd be allowed to live for X seconds until killing them, unless they're used again.

share|improve this question

1 Answer

up vote 1 down vote accepted

The connection pool will automatically kill connections which haven't been used in a while. There's no way to identify these from the SQL Server side with 100% accuracy without running the risk of the client application throwing error messages when it attempts to use the connection from the pool which it things should be open put is really closed.

What problem are you trying to solve for?

share|improve this answer
A webpage that doesn't close a connection. If the connection used is idle long enough - the page probably finished loading and forgot to close. – JNF Mar 6 at 12:35
1  
IIS will return the connection to the pool once the page load is completed automatically, even if the developer didn't close the connection manually. When load on the website drops IIS will automatically terminate connections that it doesn't think that it needs automatically. It's best to leave this up to the application tier instead of trying to be smarter than it. Are you seeing a problem or running out of threads or something? – mrdenny♦ Mar 6 at 12:38
On occasion I can't make a connection. The error message suggests this is because the pool is full. Was assuming a page where connection doesn't close, though couldn't find it. – JNF Mar 6 at 12:44
1  
Sounds like IIS just needs to allocate more connections to the pool and isn't able to do so, probably because you've hit the limit on the number of connections in the pool. Just increase the limit and you'll be good to go. codeproject.com/Articles/17768/… – mrdenny♦ Mar 6 at 12:54
Not sure that will solve it, if the same connection is opened on different instances of the page then there are a lot of open connections – JNF Mar 6 at 13:27
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.