user connections Option

Use the user connections option to specify the maximum number of simultaneous user connections allowed on Microsoft SQL Server. The actual number of user connections allowed also depends on the version of SQL Server you are using and the limits of your application or applications and hardware. SQL Server allows a maximum of 32,767 user connections.

Because 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. For example, if only 10 users are logged in, 10 user connection objects are allocated. In most cases, you need not change the value for this option. Use sp_configure to determine the maximum number of user connections that your system allows.

Using the user connections option helps avoid overloading the server with too many concurrent connections. You can estimate the number of connections based on system and user requirements. For example, on a system with many users, each user would not usually require a unique connection. Connections can be shared among users. Users running OLE DB applications need a connection for each open connection object, users running Open Database Connectivity (ODBC) applications need a connection for each active connection handle in the application, and users running DB-Library applications need one connection for each process started that calls the DB-Library dbopen function.

Important

If you must use this option, do not set the value too high, because each connection takes approximately 28 kilobytes (KB) of overhead regardless of whether the connection is being used. If you exceed the maximum number of user connections, you receive an error message and are not able to connect until another connection becomes available.

The user connections option is an advanced setting. If you are using the sp_configure system stored procedure to change the setting, you can change user connections only when show advanced options is set to 1. The setting takes effect after the server is restarted.

See Also

Tasks

How to: Set User Connections (SQL Server Management Studio)

Concepts

Setting Server Configuration Options

Other Resources

RECONFIGURE (Transact-SQL)
sp_configure (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance