Maximum User Connections for a Single User Login

greenspun.com : LUSENET : SQL Server Database Administration : One Thread

What are the resources(like memory) used for a login? Can anyone help me with answers to the follg:

What is the maximum possible connections for a single login user? For eg. How many connections can be created through sa user, for a same database and different database?

What is the advantage of creating more users and sharing the connection?

Regards

Deepa

-- Anonymous, November 21, 2001

Answers

Deepa,

I found this in the SQL Server Books Online:

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(s) and hardware. SQL Server allows a maximum of 32,767 user connections.

Because user connections is 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 should not need to change the value for this option.

You can use SQL Query Analyzer and the following Transact-SQL statement to determine the maximum number of user connections that your system allows:

SELECT @@MAX_CONNECTIONS

user connections 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 who are running OLE DB applications need a connection for each open connection object, users who are running ODBC applications need a connection for each active connection handle in the application, and users who are 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 40 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.

user connections is an advanced option. 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 stopping and restarting the server.

To set user connections

Enterprise Manager

How to set user connections (Enterprise Manager) To set user connections

Expand a server group.

Right-click a server, and then click Properties.

Click the Connections tab.

Under Connections, in the Maximum concurrent user connections (0 = unlimited) box, type or select a value from 0 through 32767 to set the maximum amount of simultaneous user connections allowed to the instance of Microsoft® SQL Server™.

Transact-SQL

SQL-DMO

See Also

RECONFIGURE

Setting Configuration Options

sp_configure

Hope this helps,

Eric

-- Anonymous, November 22, 2001


Moderation questions? read the FAQ