.Net, ADO.NET, ASP.NET, Data Sources, Error Handling, Programming

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

If you are working with ASP.NET in Visual Studio, then you may be mystified when you see an error code : Adding the specified count to the semaphore would cause it to exceed its maximum count.

This error had me wondering what exactly is going on…

Identifying the Problem

At first when I saw this error, I thought it might be due to the syntax of the query I was running. However after starting up Query Profiler I was able to run the query directly in SSMS without any issues. The problem clearly was somewhere in the application and not with the SQL query.

After investigating this, 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 first question I asked myself was what a semaphore is since I had not heard this term used before. Well, it seems that 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.

What is the cause of this problem?

So that was pretty interesting information. Basically with the error being returned, it appears that some sort of a lock was being retained and not released when my VB.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.

I’m still not able to pin down exactly what was causing this error to happen. People online speculate that similar problems appear to be caused by network issues. However, when I encountered the problem there were no noticeable network problems though, so I doubt that the network was causing the problem in my case.

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.

Why Are Connection Pools Used By Default?

So 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.

There is a high performance hit involved with establishing a connection with a database, so ADO.NET tries to increase performance by not destroying connections after a call has happened to a database. Rather, ADO.NET puts the released connection into a pool which it holds for the next time that a request to the database is made. In general this ends up returning database results much faster than if connection pooling is disabled.

Of course we have also seen the down-side to connection pooling, which is the semaphore error where ADO.NET tries to access a connection in the pool, and finds that it can’t.

In the case of my application I decided that the possible performance improvement gained by using connection pooling was outweighed by the possibility of getting ugly connection errors such as the semaphore error, so next I will explain how to ‘fix’ the semaphore error by disabling ADO.NET connection pooling.

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" />

Pretty simple, right?

Sometimes finding the reason for a problem is more difficult than fixing the problem. Now that I know what was causing the error I will find it easier to diagnose similar problems in future!


21 thoughts on “ASP.NET Error – Adding the specified count to the semaphore would cause it to exceed its maximum count”

  1. You may find this comment bit late as I see so many comments from 2013! today I faced the same error while executing one of the most stable application I developed for years. The problem seems not not be the pooling instead you need to check if there is any impersonation happening (implicit or explicit), any db connection opened in/out this impersonation and being released after impersonation was undone etc.

    This problem I was able to fix by making sure that no impersonation code is executed during the connection opening (if needed then impersonating user was retained until connection closure) and closing and now bingo!!! all sorted.

    if you think you have not done any impersonation directly in code, check your website settings (either in web.config or IIS level) to make sure that application pool identity is not uplifted to an user having more privileges (i.e. medium or high trusted user) than normal app pool identity. In such cases the IIS would impersonate the user anyway and automatically when needed and also will undo impersonation as soon as it doen’t need.

    hope this may be useful to someone…

    1. Hi, thanks for sharing your solution, I think it will definitely be useful to people looking for more information about resolving the Semaphore issue.

  2. After an hour of troubleshooting I resorted to restarting Visual Studio which fixed it. The error message is a bit bewildering. But now I know what the deal was. Thank you.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s