ADO.NET Database Pool Exhaustion issue

This post has been republished via RSS; it originally appeared at: IIS Support Blog articles.

I have recently worked on an issue that was caused by ADO.NET database pool exhaustion. This is one of the most common reasons of Timeout Expired error web applications run into.

 

The error message we saw in the dump file:

 

System.InvalidOperationException

Timeout expired. 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.

1.jpg

 

Root cause

Based on the stack trace above, I saw that the MaxPoolSize was reached for ADO.NET connections. This is a clue about a coding issue.

 

Upon checking the application code, I noticed that a SqlDataReade object is created but never closed in the authentication function. The connection itself is never closed neither. It means the application is creating a new connection each time this function is called. As a result, the connection pool is adding up quickly. The default MaxPoolSize is 100. When the application reaches this number, new requests are queued until the time-out occurs.

 

This is the reason users keep waiting for the page to load. Since there is still no availability in the connection pool after the wait, the request is timing out. Therefore, the application is redirected to the custom error page.

 

It explains why this issue happens intermittently (for some users only). If you spend enough time in the application until exhausting the connection pool, you can run into this issue with every user account.

 

Solution

Temporary solution: Recycle the application pool. It clears the connection pool.

 

Permanent solution: The ADO.NET connection function should be edited. The connection and dataReader objects should be closed after each transaction (Refer to this article:(

 

We strongly recommend that you always close the connection when you are finished using it so that the connection will be returned to the pool. You can do this using either the Close or Dispose methods of the Connection object, or by opening all connections inside a using statement in C#, or a Using statement in Visual Basic. Connections that are not explicitly closed might not be added or returned to the pool. For more information, see using Statement or How to: Dispose of a System Resource for Visual Basic.

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.