In the past couple of weeks I’ve ran into a few situations where users working on development instances ended up accidentally limiting the number of concurrent user connections that SQL Server can handle.
This, in turn, caused the developers to not be able to log into SQL Server when the number of connections went past the newly set limit.
I figured I’d write this post to outline how to troubleshoot and fix this in case anyone else has a similar issue.
What are user connections?
Basically any application connecting to an instance uses up at least one user connection slot. Take SSMS for example, just connecting to an instance, without opening a query editor window, will take up 2 user connections (one for Object Explorer and one for retrieving instance info and IntelliSense data) with each query editor window taking up one connection slot.
As per the official documentation, SQL Server allows a maximum of 32,767 concurrent user connections, but the actual number depends also on the hardware resources available on the instance’s host.
Checking and changing the maximum number of connections
The default maximum number of concurrent user connections is 0 which means unlimited.
There are two ways of checking and/or changing this limit, both of which require an instance restart to take effect:
Warning: don’t go changing this configuration option in production since it will end up causing an outage once the applications relying on those instances will hit the newly imposed limit.
From the GUI, by right clicking on the instance in object explorer, click on Properties and the option is the first one in the Connections section.
Or via T-SQL using the following query to check the value:
|
1 2 3 4 5 6 7 8 |
SELECT [name], [value], [value_in_use], [description], [is_dynamic], [is_advanced] FROM [sys].[configurations] WHERE [name] = N'user connections'; |
Notice that this configuration option is not dynamic (is_dynamic is 0) and that’s why it requires an instance restart.
Since it’s also an advanced option, using sp_configure requires 2 additional steps to enable and afterwards disable the option to show advanced options.
The following block of T-SQL will set the max number of concurrent user connections to 10:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
EXEC sys.sp_configure N'show advanced options', N'1' GO RECONFIGURE WITH OVERRIDE GO EXEC sys.sp_configure N'user connections', N'10' GO RECONFIGURE WITH OVERRIDE GO EXEC sys.sp_configure N'show advanced options', N'0' GO RECONFIGURE WITH OVERRIDE GO |
For example’s sake I’ve set the limit to 2 on my environment.
So what happens when attempting to connect?
The connection attempt will fail with error code 233 or 64, but the error message and occasionally the behavior depends on a few factors:
- connection attempt to a remote or local instance through TCP
- connection attempt to a local instance through Shared Memory
- all connection slots already in use when trying to connect
- Object Explorer takes the last available connection slot and the query editor is left hanging
In my test, I’ve made sure that all the connection slots are taken before trying to connect via SSMS to a remote instance.
Error text:
“A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 – The specified network name is no longer available.) (Microsoft SQL Server, Error: 64)”
Other error messages
In the case of a local connection through shared memory when no more connection slots are available, the error message would be:
“The client was unable to establish a connection because of an error during connection initialization process before login. Possible causes include the following: the client tried to connect to an unsupported version of SQL Server; the server was too busy to accept new connections; or there was a resource limitation (insufficient memory or maximum allowed connections) on the server. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)”
When connecting through SSMS and Object Explorer takes up the last connection slot, the error occurs when opening a query editor window or checking an object’s properties.
And for the same scenario, but Shared Memory is enabled and the instance is local, then the error message would look like this:
“A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)”
Troubleshooting and fixing
The fix requires access and admin permissions on the host where the instance resides.
First, I find out the instance’s service name through either SQL Server Configuration Manager or through Services Manager (WinKey+R -> services.msc -> Enter).
Afterwards, I stop the service from either PowerShell or Command Prompt opened as Admin.
|
1 |
net stop "SQL Server (SQL2019)" |
Then, I start it back up in single user mode (via the /m flag) and specify that only sqlcmd is allowed to connect. Since I’m doing this in command prompt, I use && to also connect through sqlcmd as soon as the service is running.
|
1 |
net start "SQL Server (SQL2019)" /m"SQLCMD" && sqlcmd -S localhost\sql2019 -E |
Once the connection is established, I am able to update the max number of concurrent user connection to its default value.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
EXEC sys.sp_configure N'show advanced options', N'1' GO RECONFIGURE WITH OVERRIDE GO EXEC sys.sp_configure N'user connections', N'0' GO RECONFIGURE WITH OVERRIDE GO EXEC sys.sp_configure N'show advanced options', N'0' GO RECONFIGURE WITH OVERRIDE GO |
I then disconnect from sqlcmd and restart the instance without the /m flag so that the changes take effect and it’s no longer in single user mode.
|
1 2 |
net stop "SQL Server (SQL2019)" net start "SQL Server (SQL2019)" |
Conclusion
Avoid messing around with the value of the user connections limit, especially since there’s very rarely a valid reason for changing it.
Good stuff!
Also, I think that accessing the server through DAC , would have worked
Thank you!
Yes, DAC would be a viable alternative to starting the instance in single user mode.
The only thing is that most shops, especially development shops, don’t have DAC enabled as part of their standard config.