HOW TO: Resolve "The Maximum Connection Limit Has Been Reached" Error Message (320728)

SUMMARY

In SQL Server 7.0 and SQL Server 2000, administrators can use the sp_configure stored procedure to modify configuration settings. One of the settings that you can modify is the user connections option. When you install SQL Server, the default value for user connections is 0 (32767 concurrent connections). Microsoft recommends that you do not change the default user connections setting.

If user connections is set to a value of 1, SQL Server does not start and the SQL Server error log (for both SQL Server 7.0 and SQL Server 2000) contains the following entry:

The maximum limit for connections has been reached

The SQL Server 7.0 error log also contains the following entry:

initconfig: Number of user connections limited to 1

This article describes how to resolve these errors.

back to the top

Stand-Alone SQL Server

To resolve these errors for SQL Server that is running on a stand-alone computer, start SQL Server with the minimum configuration, and then reset the user connections configuration value. To do so, follow these steps:

Use the following syntax to start SQL Server from a command prompt:

SQL Server 7.0:
```
sqlservr -c -f
						
```
SQL Server 2000 default instance:
```
sqlservr.exe -c -f
						
```
SQL Server 2000 named instance:
```
sqlservr.exe -c -f -s {instancename}
						
```
SQL Server will start as an application and it will run in the command prompt window.
Use Query Analyzer to connect to SQL Server. Make sure to use a logon profile that has System Administrator permissions on SQL Server.
Issue the following commands:
```
sp_configure 'user connections', 0
go
reconfigure with override
						
```
This code sets user connections back to the default setting.
In the command prompt window, press CTRL+C to quit SQL Server. Type Y to shut down SQL Server.
Restart SQL Server normally.

back to the top

Virtual SQL Server

To resolve these errors for a virtual instance of SQL Server, follow these steps:

Make sure that SQL Server and SQL Server Agent are offline.
Make sure that MS DTC is offline as well as any other application that can connect to SQL Server.
Use the following syntax to start SQL Server from a command prompt:

SQL Server 7.0:
```
sqlservr -c -f
						
```
SQL Server 2000 default instance:
```
sqlservr.exe -c -f
						
```
SQL Server 2000 named instance:
```
sqlservr.exe -c -f -s {instancename}
						
```
SQL Server will start as an application and it will run in the command prompt window.
User Query Analyzer to connect to SQL Server. Make sure to use a logon profile that has System Administrator permissions on SQL Server.

Issue the following commands:

sp_configure 'user connections', 0
go
reconfigure with override

In the command prompt window, press CTRL+C to quit SQL Server. Type Y to shut down SQL Server.
Use Cluster Administrator to bring SQL Server online.

back to the top

Troubleshooting

You may receive the "The maximum connection limit has been reached" error message when SQL Server is up and running. In this situation, the configured number of user connections is not enough to support the number of concurrent connections to the SQL Server instance. The solution is to close enough connections to allow a new connection from Query Analyzer. After you can connect, issue the following commands from Query Analyzer:

sp_configure "User Connections", 0
go
reconfigure with override

This code resets user connections to the default setting. Stop, and then start SQL Server for this change to take effect.

back to the top