2

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.

1

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.

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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