Searching out connection leaks in Microsoft SQL

Recently I was told by a developer that it was unnecessary to call the close method on a SQL connection because garbage collection would handle that for you. Unfortunately, he is only right in certain situations. Desktop applications could probably get away with letting garbage collection handle the closing of your connection objects since the lifetime of the connection could conceivably be used throughout the use of the application. A connection leak in a situation like this would likely only result in a few rogue connections with each new instance of the application. Certainly not ideal, but it's probably not going to cause a problem.

A web application is an entirely different story because a single connection leak in a page means a rogue connection every time that page is visited. If your max pool is set to say, 50 connections your entire website can be potentially be taken down if 50 visits are made within a matter of hours to a page that doesn't close all of it's connections. If you're lucky garbage collection might come along in time but crossing your fingers is not a realistic solution.

So let's say you already know you have a connection leak or you're just curious about your site. How do you check? For a smaller site just running through each page making sure that the close method is called on all new connections will probably do the trick but what if you have multiple user controls, data objects and a handful of custom libraries all which create database connections. What then?

First of all there is a convenient stored procedure that can show you all existing connections to your Microsoft SQL server called sp_who. Unfortunately since this shows all connections to the server it's not immediately useful for determining connection leaks. We'll create two other stored procedures which I called sproc_showDBConnections and sproc_showDBConnectionCount. They are:

CREATE PROCEDURE
[dbo].[sproc_ShowDBConnections]
    @DBName varchar(50)
AS
BEGIN
   CREATE TABLE #temp (spid int,
      ecid int,
      status varchar(50),
      loginame varchar(50),
      hostname varchar(50),
      blk int,
      dbname varchar(50),
      cmd varchar(50),
      request_id int);
   INSERT INTO #temp EXEC sp_who;
   SELECT * FROM #temp WHERE dbname LIKE @DBName;
   DROP TABLE #temp;
END

And more importantly

CREATE PROCEDURE [dbo].[sproc_ShowDBConnectionCount]
    @DBName varchar(50)
AS
BEGIN
   CREATE TABLE #temp (spid int,
      ecid int,
      status varchar(50),
      loginame varchar(50),
      hostname varchar(50),
      blk int,
      dbname varchar(50),
      cmd varchar(50),
      request_id int);
   INSERT INTO #temp EXEC sp_who;
   SELECT count(spid) as count FROM #temp WHERE dbname LIKE @DBName;
   DROP TABLE #temp;
END

We don't really need sproc_ShowDBConnections for this but it's a useful stored procedure none the less so I thought I would include it. Executing sproc_DBConnectionsCount returns a single number indicating the current number of connections to the specified database. But before we can determine what that number means we need to reset the application pool to clear out any rogue connections that already exist. If the website you're trying to test is being run on IIS you can do this by determining the pool from the website's properties and stopping and starting the appropriate pool. Executing the stored procedure should now return the value of zero.

The next step is to load up some page in your website and again execute the query. The number of connections should increase but not by much. There are some overhead connections that will remain open so this is normal. Next, send several requests (ex: click refresh on your browser really fast) to your page and again check the connection count. If it remains unchanged then congratulations! You have no connection leaks in the code that was executed on that page. Run through the rest of your site and continually check the number of connections. Make sure this application is the only one connecting to this database or that the other applications are inactive as any unrelated connections will screw your results.

So what do you do if you have detected a connection leak? It's possible that simply by reviewing your code you can spot it, but again what if it's a very complicated page? There are several methods but the one I prefer is using SQL profiler to determine which queries are executing on the connection that is never closed. Once I know that I can find the culprit simply by doing a search in the application for stored procedure names, table names or fields and discover the connection object they're using. The default trace in SQL profiler can be used to determine this but it includes some data that isn't really relevant so it can be difficult to read. Some slight changes can fix that.

Once you've started the trace run the page again. You should see something similar to the results I found. Notice how the SPID changes while on the same ClientProcessID, this is indicative of a possible connection leak. Simply look at the last query run on the original SPID and pick out any key table names or stored procedures that could help you find the applicable code block where this connection object was created.

I hope this has helped a few developers discover why their website was occasionally crashing. Happy hunting all.