before increasing the SQL Connection pool size

If you are using ADO.NET technology in data access layer you might have encountered error saying, MAX POOL REACHED.

The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

When you see that the error in your application logs, the natural reaction is to, try increasing the connection Max Pool Size in the SQL Connection string. But that isn't always right approach to deal with the issue.

<add key="DBConnectionString" value=" Data Source=DBServer,port;Initial Catalog=DatabaseName;Integrated Security=true;Max Pool Size=??" />

Let's stop and think for a while

Before increasing the MAX Pool Size in the SQL Connection String, give a thought on the below points:

1. When you don't specify the "MAX Pool Size" setting explicitly in the SQL Connection string, the default value is 50. i.e., already your application is configured @ Max Pool Size of 50 SQL connections.
2. Then to ask yourself a question, does your app(single instance) really need approximately 50 simultaneous connections with Database?. If your application (single instance) is not dealing with such volume of data access operations, it more likely that you have got SQL Connection Leak problem.

Deciding on the approach to deal with Max Pool Reached error

Increasing the Connection Pool size

If your application (single instance) does really need high number of data access operations, tuning up-to more than 50 simultaneous connections with Database, then to be on safer side, try to increase this value to a sufficiently large number.

<add key="DBConnectionString" value=" Data Source=DBServer,port;Initial Catalog=DatabaseName;Integrated Security=true;Max Pool Size=100" />

** In the above case, the Max Pool Size has been increased from default 50 to 100 simultaneous connections with Database.

Fixing the SQL Connection Leak issue

If you are getting max pool reached error & if you think your application isn't dealing with such a higher number of simultaneous data access operation; then you might have got SQL Connection Leak issue in your application code. In that case, continue reading the post further.

What is a SQL Connection leak?

When you open a Database connection, you are required to ensure that connection gets closed once you are done with your data access logic. If, in any of the code execution flow, including exceptions scenarios, if database connections are not closed then its referred as Connection Leak issue.

When number of Database Connection Leaks reaches the MAX POOL SIZE, then your application call to OPEN SQL Connection will result in "MAX POOL SIZE REACHED" exception.

So solution is to close the opened SQL connections without a miss and also most importantly to close the connections as soon as possible.

How to fix SQL Connection leak

• With SqlCommand.ExecuteNonQuery