SQL Server max connection settings

Question

On a machine that is running IIS 6.0 and SQL Server 2005, where are the different locations that can limit the max number of database connections?

Answer 1

If by "number of connections" you mean the size of the connection pool, then this applies:

A connection pool is created for each unique connection string. When a pool is created, multiple connection objects are created and added to the pool so that the minimum pool size requirement is satisfied. Connections are added to the pool as needed, up to the maximum pool size specified (100 is the default). Connections are released back into the pool when they are closed or disposed.

When a SqlConnection object is requested, it is obtained from the pool if a usable connection is available. To be usable, a connection must be unused, have a matching transaction context or be unassociated with any transaction context, and have a valid link to the server.

The connection pooler satisfies requests for connections by reallocating connections as they are released back into the pool. If the maximum pool size has been reached and no usable connection is available, the request is queued. The pooler then tries to reclaim any connections until the time-out is reached (the default is 15 seconds). If the pooler cannot satisfy the request before the connection times out, an exception is thrown.

Source: SQL Server Connection Pooling (ADO.NET)

The default size of connection pools is 100. You can change that size in in the connectionstring itself, but you should have a good reason to do that. In most cases where you run out of connections, they are leaking (=not properly closed) in the application.

Answer 2

If you go to the server properties in Management studio, on the connections page, there is a Maximum number of concurrent connections setting.

Right click on the server in object explorer and select properties. Click Connections in the left-hand pane.

Answer 3

Properties on the SQL server instance. Right-click on the instance in Mgmt Studio and get properties the click on Connections. Are you having a specific problem with connections?