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.
Sample Application
Let us start with a simple C# WinForms application which has the following UI.
The Connect button will have the below coding behind it:
private void Connect_Click(object sender, EventArgs e)
{
int i = 0;
while (1 == 1) // Loop indefinitely
{
//Connection is create and connectiong to database
string conn = “Data Source=.;Initial Catalog=UserConnection; Integrated Security=SSPI;enlist=false”;
SqlConnection sqlConn = new SqlConnection(conn);
sqlConn.Open();
//Store procedure insert_connection_details is executed,
//looping instance value (i) will be passed
SqlCommand com = new SqlCommand(“insert_connection_details”, sqlConn);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.Add(new SqlParameter(“@connectionId”, SqlDbType.Int)).Value = i;
com.ExecuteNonQuery();
//if the Close check box is selected onlyConnection will be closed
if (chkClose.Checked)
sqlConn.Close();
//if the dealy is not 0, there will be delayed of millisecond of given value in Delay box
if (txtDelay.Text != “0”)
{
Thread.Sleep(Convert.ToInt32(txtDelay.Text));
}
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.
CREATE TABLE [dbo].[user_connection](
[ConnectionID] [int] NOT NULL,
[SPID] [int] NULL,
[DateTime] [datetime] NULL,
CONSTRAINT [PK_user_connection] PRIMARY KEY CLUSTERED
(
[ConnectionID] ASC
)
)
So after the table is created, the next step is to create the stored procedure:
CREATE PROC [dbo].[insert_connection_details]
@connectionId int
AS
SET NOCOUNT ON
INSERT INTO user_connection
SELECT
@connectionId,
@@SPID,
GETDATE()
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 following error.
We will further analyze this error is at the end of this article.
After this error if we look at the perfmon output you may see something like this:
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 result set.
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.
SELECT SPID
,COUNT(1) SPID_COUNT
FROM user_connection
GROUP BY SPID
ORDER BY 1
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 – the Application is failing when it reaches unique SPID equal 100 not the number of connections.