How to track database connection leaks

We have an app which seems to have connection leaks (SQL Server says that the max pool size has been reached). I am alone on my dev machine (obviously), and just by navigating the app, I trigger this error. The SQL Server Activity monitor shows a great number of processes using my database.

I want to find which files open connections but do not use it. I was thinking of using something like grep to, for each file, count the number of ".Open()" and the number of ".Close()", and get the file for which the numbers are not equal. Is it realistic?

Bonus question: do the processes found in SQL Server Activity Monitor correspond to the connections? If not, how do I find out how many connections are open on my database?

The app is in asp.net (vb) 3.5, with SQL Server 2005. We currently do not use LINQ (yet) or anything like that.

Thanks

Upvotes: 8

Answers (3)

Uttam Ughareja

Reputation: 1355

Here is what works for me, unfortunately i forgot from where i took it.

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
      and db_name(s.database_id) = 'your_dbname'
group by
      host_name,
      host_process_id,
      program_name,
      database_id
order by
      count(*) desc;

-----------------------
declare @host_process_id int = 15148;
declare @host_name sysname = N'your_iis_appname';
declare @database_name sysname = N'your_dbname';

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;

Upvotes: 0

Vlad Mihalcea

Reputation: 153760

The best way to tackle connection leaks is to do it during testing.

You can use an automated utility so that each test verifies if there is a connection leak.

@BeforeClass
public static void initConnectionLeakUtility() {
    if ( enableConnectionLeakDetection ) {
        connectionLeakUtil = new ConnectionLeakUtil();
    }
}
 
@AfterClass
public static void assertNoLeaks() {
    if ( enableConnectionLeakDetection ) {
        connectionLeakUtil.assertNoLeaks();
    }
}

The ConnectionLeakUtil looks like this:

public class ConnectionLeakUtil {
 
    private JdbcProperties jdbcProperties = JdbcProperties.INSTANCE;
 
    private List idleConnectionCounters = 
        Arrays.asList(
            H2IdleConnectionCounter.INSTANCE,
            OracleIdleConnectionCounter.INSTANCE,
            PostgreSQLIdleConnectionCounter.INSTANCE,
            MySQLIdleConnectionCounter.INSTANCE
    );
 
    private IdleConnectionCounter connectionCounter;
 
    private int connectionLeakCount;
 
    public ConnectionLeakUtil() {
        for ( IdleConnectionCounter connectionCounter : 
            idleConnectionCounters ) {
            if ( connectionCounter.appliesTo( 
                Dialect.getDialect().getClass() ) ) {
                this.connectionCounter = connectionCounter;
                break;
            }
        }
        if ( connectionCounter != null ) {
            connectionLeakCount = countConnectionLeaks();
        }
    }
 
    public void assertNoLeaks() {
        if ( connectionCounter != null ) {
            int currentConnectionLeakCount = countConnectionLeaks();
            int diff = currentConnectionLeakCount - connectionLeakCount;
            if ( diff > 0 ) {
                throw new ConnectionLeakException( 
                    String.format(
                        "%d connection(s) have been leaked! Previous leak count: %d, Current leak count: %d",
                        diff,
                        connectionLeakCount,
                        currentConnectionLeakCount
                    ) 
                );
            }
        }
    }
 
    private int countConnectionLeaks() {
        try ( Connection connection = newConnection() ) {
            return connectionCounter.count( connection );
        }
        catch ( SQLException e ) {
            throw new IllegalStateException( e );
        }
    }
 
    private Connection newConnection() {
        try {
            return DriverManager.getConnection(
                jdbcProperties.getUrl(),
                jdbcProperties.getUser(),
                jdbcProperties.getPassword()
            );
        }
        catch ( SQLException e ) {
            throw new IllegalStateException( e );
        }
    }
}

The IdleConnectionCounter implementations can be found in this [blog post][1], and the MySQL version like this:

public class MySQLIdleConnectionCounter implements IdleConnectionCounter {
 
    public static final IdleConnectionCounter INSTANCE = 
        new MySQLIdleConnectionCounter();
 
    @Override
    public boolean appliesTo(Class<? extends Dialect> dialect) {
        return MySQL5Dialect.class.isAssignableFrom( dialect );
    }
 
    @Override
    public int count(Connection connection) {
        try ( Statement statement = connection.createStatement() ) {
            try ( ResultSet resultSet = statement.executeQuery(
                    "SHOW PROCESSLIST" ) ) {
                int count = 0;
                while ( resultSet.next() ) {
                    String state = resultSet.getString( "command" );
                    if ( "sleep".equalsIgnoreCase( state ) ) {
                        count++;
                    }
                }
                return count;
            }
        }
        catch ( SQLException e ) {
            throw new IllegalStateException( e );
        }
    }
}

Now, when you run your tests, you'll get a failure when a connection is being leaked:

:hibernate-core:test
 
org.hibernate.jpa.test.EntityManagerFactoryClosedTest > classMethod FAILED
    org.hibernate.testing.jdbc.leak.ConnectionLeakException

Upvotes: 7

Filip De Vos

Reputation: 11908

When looking at the code from the SQL Server side you can run the following query to get a view on which queries are last run on sleeping connections. (open connections which are doing nothing)

SELECT ec.session_id, last_read, last_write, text, client_net_address, program_name, host_process_id, login_name
FROM sys.dm_exec_connections  ec
JOIN sys.dm_exec_sessions es
  ON ec.session_id = es.session_id
CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) AS dest
where es.status = 'sleeping'

From the application side you can debug with sos.dll as described in the following articles:

If you need more information on how to use windbg, these articles are a good intro:

Upvotes: 12

How to track database connection leaks

Answers (3)

Related Questions