ASP.NET Error – Adding the specified count to the semaphore would cause it to exceed its maximum count

 ASP.NET Error – Adding the specified count to the semaphore would cause it to exceed its maximum count

The Problem

The problem clearly was somewhere in the application and not with the SQL query.

 it appears that this error is related to ASP.NET’s ADO.NET connection pool manager. When you put together a connection string in your web.config file, you probably won’t be thinking about connection pooling, but by default this will be enabled. The connection pool manager will attempt to find a free connection, but if none are available it will throw the error we saw.

 What is a Semaphore in SQL Server?

 The definition of semaphore is just a locking mechanism used by SQL Server behind the scenes to prevent more than one task from accessing a data structure that is currently in use. In particular SQL Server protects user log caches using semaphores.

The cause of this problem.

Basically with the error being returned, it appears that some sort of a lock was being retained and not released when my .NET application was communicating with SQL Server. It was definitely not a problem with long running queries or forgetting to clean up the connection object after execution was complete.
Bottom line is that the semaphore locking issue appears to be  related to ASP.NET’s connection pooling. Resources were being locked in the pool and then were not being released, so I would see the semaphore error when my application was trying to access the application pool and no free resources were available.


since ADO.NET connection pools seem to be a possible point of failure, the question remains: why are connection pools enabled by default? The answer is performance.

Fixing the Problem

The simplest way to fix the ADO.NET semaphore error is to disable connection pooling in the connection string of your web.config file.
Here is an example of what a default connection string might look like. Although it doesn’t specify a connection pooling option, this is enabled by default:

<add name="testConnection" connectionString="Data Source=MyDBServer;

 Initial Catalog=MyDatabase;Persist Security Info=True;

 User ID=testUserId;Password=TestPassword;"
 providerName="System.Data.SqlClient" />

Now to disable pooling and get rid of the error message we were seeing, we simply append the directive Pooling=False to the end of our connection parameters as follows:

<add name="testConnection" connectionString="Data Source=MyDBServer;

 Initial Catalog=MyDatabase;Persist Security Info=True;

 User ID=testUserId;Password=TestPassword;Pooling=False;"
 providerName="System.Data.SqlClient" />