Microsoft KB Archive/320728

= HOW TO: Resolve &quot;The Maximum Connection Limit Has Been Reached&quot; Error Message =

Article ID: 320728

Article Last Modified on 1/12/2004

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2000 64-bit Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q320728



IN THIS TASK
SUMMARY
 * Stand-Alone SQL Server
 * Virtual SQL Server

Troubleshooting REFERENCES



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.</li> Make sure that MS DTC is offline as well as any other application that can connect to SQL Server.</li>  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. </li> User Query Analyzer to connect to SQL Server. Make sure to use a logon profile that has System Administrator permissions on SQL Server.</li>  Issue the following commands: sp_configure 'user connections', 0 go reconfigure with override </li> In the command prompt window, press CTRL+C to quit SQL Server. Type Y to shut down SQL Server.</li> Use Cluster Administrator to bring SQL Server online.</li></ol>

back to the top

Troubleshooting
You may receive the &quot;The maximum connection limit has been reached&quot; 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 &quot;User Connections&quot;, 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

<div class="references_section">