Monitor User Connections in SQL Server
Applications typically make connections to the SQL Server databases in
large numbers but what if those connections are not successfully closed? How you can
monitor them? This short article describes the methods we can use to monitor unclosed connections.
Let us start with a simple C# WinForms application which has the following UI.
The Connect button will have the below coding behind
private void Connect_Click(object
sender, EventArgs e)
int i = 0;
while (1 == 1) //
//Connection is create and connectiong to database
string conn = “Data
SqlConnection sqlConn = new SqlConnection(conn);
//Store procedure insert_connection_details is
//looping instance value (i) will be passed
SqlCommand com = new
com.CommandType = CommandType.StoredProcedure;
com.Parameters.Add(new SqlParameter(“@connectionId”, SqlDbType.Int)).Value = i;
//if the Close check box is selected onlyConnection
will be closed
//if the dealy is not 0, there will be delayed of millisecond of
given value in Delay box
if (txtDelay.Text != “0″)
i = i + 1;
Since this code is very simple and fully commented you should understand what is happening with the connect button.
From the SQL Server side, as seen from the above code, there
should be a stored procedure as well as a table to store this information.
[int] NOT NULL,
CONSTRAINT [PK_user_connection] PRIMARY KEY CLUSTERED
So after the table is created, the next step is to create the stored procedure:
So now we are ready for the testing. For testing, we will use
standard tools like perfmon and SQL Server Profiler. In perfmon we will be
using the User Connections counter of the SQL Server General Statics
object. Meanwhile, in the SQL Profiler, the Audit Login event will be captured.
So are you ready to go.
Scenario 1: Not closing the database connection.
When we executed the above code, the code will fail with the
We will further analyze this error is at the end of this
After this error if we look at the perfmon output you may see something like
Before running the application, the count was 5
which is caused by system processes which are connected to SQL Server. When
the application starts, the number of connections starts to increase rapidly and
after reaching 105 it stops.
If you check the profiler or the table, you will
see there are more than 100 connections that have been made to the databases. To be
exact, there are 304 records in the table.
Let’s execute few queries against this table.
If you simply run the SELECT * statement, you will see following
Here you can see that SPID is not a unique number and it is
repeating. For example, SPID 57 can be seen in two places. Similarly, other SPIDs
are also duplicated.
The above result shows that SPID is used multiple times. For
example, SPID 56 is used for 6 times, 68 is used 4 times etc.
However, total count for rows here is exactly matches 100. There you are –
is failing when it reaches unique SPID equal 100 not the number of