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

Article translations Article translations
Article ID: 320728 - View products that this article applies to.
This article was previously published under Q320728
Expand all | Collapse all

On This Page

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.

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:
  1. 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.
  2. Use Query Analyzer to connect to SQL Server. Make sure to use a logon profile that has System Administrator permissions on SQL Server.
  3. Issue the following commands:
    sp_configure 'user connections', 0
    go
    reconfigure with override
    						
    This code sets user connections back to the default setting.
  4. In the command prompt window, press CTRL+C to quit SQL Server. Type Y to shut down SQL Server.
  5. Restart SQL Server normally.

Virtual SQL Server

To resolve these errors for a virtual instance of SQL Server, follow these steps:
  1. Make sure that SQL Server and SQL Server Agent are offline.
  2. Make sure that MS DTC is offline as well as any other application that can connect to SQL Server.
  3. 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.
  4. User Query Analyzer to connect to SQL Server. Make sure to use a logon profile that has System Administrator permissions on SQL Server.
  5. Issue the following commands:
    sp_configure 'user connections', 0
    go
    reconfigure with override
    					
  6. In the command prompt window, press CTRL+C to quit SQL Server. Type Y to shut down SQL Server.
  7. Use Cluster Administrator to bring SQL Server online.

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.

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
319942 HOW TO: Determine Proper SQL Server Configuration Settings

Properties

Article ID: 320728 - Last Review: January 12, 2004 - Revision: 3.5
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 64-bit Edition
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbhowtomaster KB320728

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com