Take the 2-minute tour ×
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.

I would like to know if Too many Dormant Users/sessions on a SQL Server instance can force SQL server to not to create new connections or give connection denied error to the application which is trying to connect to server. Or what is the harm of having too many (70-100) dormant sessions/users?

Thanks in advance.

share|improve this question

2 Answers 2

The maximum number of user connections by default is 32,767. So it doesn't sound like you're reaching the cap. It is possible to alter that limit though.

But it is worth noting that it is best practice not to leave connections open, not just because the idle state, but it can get messy and mixed up. It is best to open a connection when you require access to the database, and then immediately close it once you are done. Connection pooling takes place, so there would be little to no gain of keeping a connection open and hope other code properly closes it.

share|improve this answer

If you mean "sleeping" in sysprocesses, then this is most likely OK.

Your calling code will use connection pooling to "logically" open and close connections. This avoids overhead of actually opening and closing connections which is expensive.

Examples: MSDN for .net4 and for JDBC

share|improve this answer

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.