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.
Best Answer
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.