Max Connection Pool capped at 100 I'm running SQL Server 2008 R2 SP1, on a Windows Server 2008 box. I have a .NET script running from Visual Studio 2010 that does the following:


Reaches into the database
Makes a change
Iterates


The total number of times it will iterate is 150, however it is stopping at 100 connections and I can't figure out why. I could adjust my script to just use a single thread, but I'd prefer to know where I'm missing a max connection setting as that will be more useful to know for future reference.

Here's where I've checked so far:


SQL Connection String in Visual Studio 2010 (it's set to 1000)
SSMS Database instance connection properties (it's set to 0 [infinity] user connections)
Googled some information on Server 2008, it looks like it can handle more than 100 connections
Stepped through my code alongside SP_WHO2 which gives more information on logical connections, seeing that the # of connections starts at 52 and the script errors with the "Max Pooled Connections reached" error at 152 logical connections.
Changed the connection string to use Data Source=PerfSQL02;Initial Catalog=Masked;Integrated Security=True;Max Pool Size=1000


I'm not sure where else to check, I know I have a lot of moving parts here but I'm getting the feeling I'm just missing a max pool setting somewhere.
In your connection string, specify max pool size=&lt;your desired max pool size&gt;.  So in other words, if you want to change max pool size to the value 500, your connection string could resemble this:

"data source=your_server; initial catalog=your_db; trusted_connection=true; max pool size=500"


Obviously I'm assuming so much with your other parameters, but this should give you a good idea how to proceed.  Connection pooling is client-side provider enforcement.  It's the client that will need to specify this max pool size setting, via the connection string.

Also, make sure you are closing or disposing of your connections properly, otherwise you will tie up connections.  Something like this (C#):

string connectionString = "data source=your_server; initial catalog=your_db; trusted_connection=true; max pool size=500";

using (SqlConnection dbConn = new SqlConnection(connectionString))
{
    using (SqlCommand sqlCmd = new SqlCommand())
    {
        sqlCmd.Connection = dbConn;
        sqlCmd.CommandText = "select 1;";

        // ... so on and so forth
    }
}


The using block (in C#) calls IDisposable.Dispose() upon completion.  You could also implement SqlConnection.Dispose() or SqlConnection.Close() in the finally block of a try/catch/finally block.

Reference:  MSDN Documentation on SqlConnection.ConnectionString property