Find Database Connection Leaks in Your Application

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:

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

Connection Pools

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:

using (SqlConnection conn = new SqlConnection(connectionString)) 
{
  conn.Open();
  // etc...
}

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:

select count(*) as sessions,
         s.host_name,
         s.host_process_id,
         s.program_name,
         db_name(s.database_id) as database_name
   from sys.dm_exec_sessions s
   where is_user_process = 1
   group by host_name, host_process_id, program_name, database_id
   order by count(*) desc;

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?

declare @host_process_id int = 1508;
  declare @host_name sysname = N'SERV4102';
  declare @database_name sysname = N'My_Database';
 
  select datediff(minute, s.last_request_end_time, getdate()) as minutes_asleep,
         s.session_id,
         db_name(s.database_id) as database_name,
         s.host_name,
         s.host_process_id,
         t.text as last_sql,
         s.program_name
    from sys.dm_exec_connections c
    join sys.dm_exec_sessions s
         on c.session_id = s.session_id
   cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) t
   where s.is_user_process = 1
         and s.status = 'sleeping'
         and db_name(s.database_id) = @database_name
         and s.host_process_id = @host_process_id
         and s.host_name = @host_name
         and datediff(second, s.last_request_end_time, getdate()) > 60
   order by s.last_request_end_time;

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.