We recently were surprised by a number of exceptions our application threw. Our application was failing when trying to Open a SqlConnection. The exceptions looked like this:
Remember that .Net uses connection pools to help avoid the overhead of establishing a connection on every query. Connection pools are maintained for every connection string and by default the number of connections in the pool is capped at a hundred. One hundred connections are usually sufficient. We've never had a problem with this exception before and our servers weren’t any busier than usual so we were hesitant to increase the value of MaxPoolSize. We began to suspect database connection leaks.
Database Connection Leaks
Just like memory leaks, database connection leaks can occur if you don't dispose of your database connections in a timely manner. SqlConnections are IDisposable so it’s a best practice to use the using statement:
As soon as you're done with the SqlConnection, it’s disposed and the actual connection immediately returns to the connection pool so it can be used by someone else. Otherwise the connection remains in use until the process ends or garbage collection cleans it up.
Finding Your Connection Leaks
So, if your application experiences connection timeouts because of a database connection leak, the stack traces may not help you. Just like an out-of-memory exception due to a memory leak the stack trace has information about the victim, but not the root cause. So where can you go to find the leak?
Even though database connection leaks are a client problem, you can find help from the database server. On the database server, look at connections per process per database to get a rough estimate of the size of each pool:
Program name, host name, process id and database name are usually good enough to identify connections coming from the same connection pool.
This leads me to ask a few more questions about pools with many connections. Given a pool, are there sessions that have been sleeping for a while and, if so, how long have they been sleeping and what was the last SQL statement they executed?
The text can now be used to search your application's code base to find where you may have a database connection leak.
These queries are useful for troubleshooting a database connection leak and they can also be used to create a monitor or health check.
Dispose your disposables, use those Usings, seal those leaks!
Article Courtesy : Michael blogs as "Database Whisperer" at michaeljswart.com.
Post a Comment