SQL Server Error 233: How to Set the User Connections Server Configuration option in SQL Server?
Today, I got a requirement to limit the number of concurrent user connections on a SQL Server Instance. SQL Server provides an option “user connections” in server level configurations to set or limit the maximum number of simultaneous user connections to the SQL Server Instance. Today, I am going to discuss about user connections server configuration and also a fix for SQL Server Error 233 that arise because of this server configuration.
User Connections Server Configuration
This option user connections is a dynamic (self-configuring) option, SQL Server adjusts the maximum number of user connections automatically as needed, up to the maximum value allowable. SQL Server allows a maximum of 32767 user connections. The default value of this option is 0 that means SQL Server can use maximum number of allowed connections which is 32767.
Best way to use this option to avoid the overloading the server with too many concurrent connections. Ideally we should not set this value too high because each connection will put some load to the server whether it will be using or not. If all defined number of connections are in use or connected, you receive error 233 and will not be able to connect until another connection becomes available. I will explain about how to fix error 233 in below section of this article.
Set User Connections Server Configuration using T-SQL
Let’s check the current value of this option on SQL Server Instance. Run below command to see the details.
EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE ; GO EXEC sp_configure 'user connections'
You can see the current config value is set to 0 that means this instance is set for maximum allowed connections. Also we can see the maximum allowed connections are shown 32767.
Now you can set the value as per your need. I have set the value of user connection to 1000 to limit its number of concurrent connections. Run below command to get this done.
EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE ; GO EXEC sp_configure 'user connections', 1000 ; GO RECONFIGURE; GO
See the below screenshot which has changed the value from 0 to 1000.
Check the value again by running same command which we have executed in first screenshot to validate this change. You can see config value is now showing as 1000.
Note: You must restart the SQL Server machine to apply this change to take effect.
Set User Connections Server Configuration using GUI to fix Microsoft SQL Server Error 233
If all defined number of connections are in use or connected, you will receive below error message and will not be able to connect until another connection becomes available.
A connection was successfully established with the server, but then an error occurred during the login process.(provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233).
If you will look into SQL Server error log, you will find below error message for the same issue.
Could not connect because the maximum number of 'XX' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: XX.XX.XX.XX]
We can fix SQL Server Error 233 using below three options.
- Set the user connections option to 0 to enable maximum number of allowed connection.
- Increase the value of user connections so that SQL Server can accept the new connections.
- You can also close your existing connections so that new connections can be accepted by SQL Server. But this is not a permanent solution.
This time let’s make this change using GUI so that you will be aware of both methods. I have already discussed T-SQL method in above section.
Connect to SQL Server Instance in SQL Server Management Studio (SSMS). Right click on SQL Server Instance in SSMS and choose properties. Server Properties window will appear to make server level changes. Click on “Connections” tab from left side pane. You can see below screen. You can see the value is 1000 in right side pane which we set in above steps.
Now revert this value to 0 to accept maximum number of allowed connection. We can also fix error 233 which you might get if maximum number of connections will be exceeded.
Now again you can validate the changes by running same command. You will see value of user connections will be showing as 0 now, that means you will not face any issue related to error 233 anymore. Restart the server to apply this change into effect.
If you are still facing SQL Server error 233 then I would recommend you to read below article. I have described another aspect for Error 233 in below article.
- SQL Server Error 15123: The Configuration Option does not exist
- How to change Network Packet size in SQL Server?
- Change Database Default Locations in SQL Server
- SQL Server DBA Interview Questions & Answers
Latest posts by Manvendra Deo Singh (see all)
- Understanding Hybrid Buffer Pool in SQL Server - December 30, 2018
- Fix:VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’ - August 30, 2018
- SQL Server Error 5184: Cannot use file ‘I:\Path\Techyaz.mdf’ for clustered server - August 29, 2018