If you don’t close your database connections you can take down your server in a few minutes, that’s fact! But closing database connections is something that beginners can very easily forget. Here I will show you how to monitor database connections to SQL Server and ensure that your code is not draining the connection pool resources.

NOTE: You will need SQL Server installed on the machine that you are planning to monitor database connections on.

The connection pool is basically a collection of database connection objects that SQL Server keeps alive so that it can serve you database requests as efficiently as possible. It is normal for SQL Server to keep a few alive at once, but when a request cannot be served from the current pool a new connection object is created and served to the client. Once the connection object is closed by the client it is released and returned back to the pool, ready to serve the next request.

If you are following good practices then your database connections should remain open for the absolute minimal amount of time possible, allowing thousands of requests to be served by a relatively small pool of connection objects. I’ll create an open database connection here which you can use to duplicate the pool being starved.

1
2
3
4
5
6
7
8

using System.Data.SqlClient;
 
        for (int i = 0; i < 200; i++)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = "Server=LOCALHOST;Database=yourdatabase;UID=username;PWD=password;";
            conn.Open();
        }

In order to track the connections being used we will use Performance Monitor (perfmon), this shows us a realtime graphical representation of our connection pool. Lets get started…

STEP 1
Goto Start > Run > type “perfmon” and click OK. This will open the performance console. There will already be a few system operations being tracked automatically which you don’t need to concern yourself with, but they give you an idea of how data is represented by perfmon.

STEP 2
Right click in the graph area and select “Add Counters”

STEP 3
From the Performance Object dropdown list select your SQL Server instance’s General Statistics. It may be named something like
SQLServer:General Statistics or
MSSQL$SQLEXPRESS: General Statistics

STEP 4
Once selected your counter list (just below the downdown list) will be updated with new options for monitoring. Scroll to the bottom of this list and you will see “User Connections”. Select this item and click Add. Your performance monitor graph should now be showing User Connections.

STEP 5
Run the “unclosed database connection code” supplied above (or your own version) while watching the monitor. You will see your user connection count rising until it hits 100, at which point your application will fail with the error below. Any other server resources requiring database connections will be presented with the same error.

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.

Lets run the code above again with properly closed connection’s. Simply add conn.close() on the line after conn.open().

1
2
3
4
5
6
7
8
9

using System.Data.SqlClient;
 
        for (int i = 0; i < 200; i++)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = "Server=LOCALHOST;Database=yourdatabase;UID=username;PWD=password;";
            conn.Open();
            conn.Close();
        }

Before we do this you should notice that your connection pool is still running at maximum on Performance Monitor. These connection objects will begin to be disposed of by SQL Server in time as it releases unused resources from the pool, but we need to reset the pool right now.

Visual Studio Server: Right click the current instance of ASP.NET Development Server on your taskbar and select Stop;
IIS: Open command prompt (start > run > cmd), type iisreset and press return

Run your code again, you will notice this time around that the user connections sit at a steady flat rate and the pool can handle all the requests without exception. This shows your connections are all closing properly and SQL Server is returning those connection back to the Connection Pool to be used by the next client waiting.

If you wish you can save this performance monitor to your desktop for fast access in future. Select File > Save As… from the menu and save it. Now you can easily monitor the connection pool demands of your applications ensuring you don’t have any unclosed connections draining your database resources.