Article ID: 320728 - Last Review: January 12, 2004 - Revision: 3.5

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

View products that this article applies to.
System TipThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.
This article was previously published under Q320728

On This Page

Expand all | Collapse all

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:
  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.
Back to the top

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.
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

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
319942  (http://support.microsoft.com/kb/319942/EN-US/ ) HOW TO: Determine Proper SQL Server Configuration Settings
Back to the top
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 64-bit Edition
  • Microsoft SQL Server 7.0 Standard Edition
Back to the top
Keywords: 
kbhowtomaster KB320728
Back to the top