Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Can you post your connection string? Please see my answer. That should be your remedy (this value will be specified in the connection string. System.Data.SqlClient defaults to 100, which is why you are see exhaustion of the connection pool). –  Thomas Stringer Oct 8 '13 at 16:47

2 Answers 2

up vote 3 down vote accepted

SQL Server allows a maximum of 32767 connections by default. It can be altered using sp_configure. To view your current configuration for this setting, use the following query:

select * from sys.configurations
where name ='user connections'

By default, you should see a maximum of 32767, value_in_use equal to 0 (use the default settings). If this has been altered, you can reconfigure SQL Server to use other values as described in the link.

You should also review how many connections are actually being made, as there might be more activity outside your application (or your application is making more connections than you think). You will want to look at General Statistics -> Logical Connections in either perfmon or query the values in sys.dm_os_performance_counters (cntr_value will show the current point in time value):

select * from sys.dm_os_performance_counters
where counter_name ='User Connections'
share|improve this answer
    
Yep, I already checked the user connections (it's the first thing I went to, and is the easiest to find both in UI and in Microsoft's documentation. I also stepped through my code and kept an eye on the number of logical connections via SSMS by using SP_WHO2, which gives a decent amount of information about the logical connections. Idling, my server has 51 connections. When the script fails, it has build 100 more connections. That's how I've gotten to where I am now. –  Sean Long Oct 8 '13 at 16:34
    
I clarified my question a bit more with steps I've taken. It might not be a setting in SQL at all, which is why I started looking at Windows and Visual Studio settings. –  Sean Long Oct 8 '13 at 16:37
1  
If you've checked all that and it still fails at 100 connections, the answer lies outside of the database settings. FYI, those 51 connections are all the system processes (+1 for yourself). –  Mike Fal Oct 8 '13 at 16:37
    
Just to provide some closure, that last comment ended up being correct. I took a closer look at why those 100 connections were being created and found a leak in the code. After fixing that it works fine. The above information is all still really useful though, and I hope others will find it useful as well. –  Sean Long Oct 10 '13 at 13:52

In your connection string, specify max pool size=<your desired max pool size>. 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

share|improve this answer
    
Here is the connection string, updating my question too. Data Source=PerfSQL02;Initial Catalog=Masked;Integrated Security=True;Max Pool Size=1000 –  Sean Long Oct 8 '13 at 16:50
2  
Is that your connection string everywhere? The reason I ask, is because different connection strings are going to be different connection pools. –  Thomas Stringer Oct 8 '13 at 16:58
    
That is a very, very good question. I checked 3 connection strings so far, but they're all pushing for a high max pool. I'll see if I can find any other string that might cause it. –  Sean Long Oct 8 '13 at 17:17
    
If you just debug the execution, you should be able to look at what the runtime value of SqlConnection.ConnectionString is for the particular operation. That'd be the easiest way. If max pool size isn't there, it's 100 then. –  Thomas Stringer Oct 8 '13 at 18:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.