Leftover Connections in Connection Pool (connection leak)

[hiriumi]

This never happened before in .NET Framework 1.0 nor .NET Framework
1.1, but connection leak happens if you don't close the connection when
you use SqlTransaction. I would like to share this information with the
MS dev community.

We had this issue of SQL Server performing very poorly while running
our application. My colleague found out that connection leak was
happening whenever the .NET code executed transactions. So I created a
small console program that does a transaction like the following.

Imports System.Data
Imports System.Data.SqlClient

Module Module1

Sub Main()

Begin:
Dim Conn As SqlConnection = GetConnection()
Conn.Open()
Dim Trans As SqlTransaction =
Conn.BeginTransaction(IsolationLevel.ReadUncommitted)

Dim Cmd As New SqlCommand("tblTest_ins", Trans.Connection, Trans)
Cmd.CommandType = CommandType.StoredProcedure

For i As Integer = 1 To 50
Console.WriteLine("Executing stored proc. (" & i.ToString() & ")")
Dim parTestCol As New SqlParameter()
With parTestCol
.ParameterName = "@TestCol"
.Direction = ParameterDirection.Input
.SqlDbType = SqlDbType.NVarChar
.Size = 50
.SqlValue = "TestValue " & DateTime.Now.ToString()
End With

Cmd.Parameters.Add(parTestCol)
Cmd.ExecuteNonQuery()

Cmd.Parameters.Clear()
Next

Trans.Commit()

Console.WriteLine("Execution Completed")

Dim Entry As ConsoleKeyInfo = Console.ReadKey()
If Entry.Key = ConsoleKey.Y Then
GoTo Begin
Else
Return
End If
End Sub

Private Function GetConnection() As
System.Data.SqlClient.SqlConnection
Dim Conn As New
SqlConnection("server=(local);database=Test;Pooling=true;user
id=sa;password=whatever;Application Name=HelloConnPool;connection
reset=true;")
Return Conn
End Function

End Module

I ran this code many times, and connection leak was happening. So I
added Conn.Close() right after Trans.Commit(), then the leak was gone.
Well, I could have done Trans.Connection.Close(), but the thing was
that right after the transaction was committed, Connection property was
null. So as we have a data layer that doesn't expose the underlying
connection, we had to define a variable as SqlConnection and hold onto
the reference to the connection from the transaction and the close it
after commit.

I hope I explained this issue well, but this never happened in .NET
Framework 1.1. We converted our code from 1.1 to 2.0, and didn't change
a thing, but this issue came out. I hope this will help people who
experience the same kind of issue. If you have any question, please
just post it here.

 

[Miha Markic [MVP C#]]

So, what exactly made you think that creating a new connection without
closing previous ones wouldn't create a new physical connection?
Your code was flawed before and it probably working due to some strange
reason.
Connection has to be closed before it gets out of scope or even better, asap
it isn't required anymore. BTW the same rule is valid for all classes that
implements IDisposable
Perhaps by reading help file you would know this without loosing time...

"If the SqlConnection goes out of scope, it remains open. Therefore, you
must explicitly close the connection by calling Close or Dispose. Close and
Dispose are functionally equivalent. If the connection pooling value Pooling
is set to true or yes, the underlying connection is returned back to the
connection pool. On the other hand, if Pooling is set to false or no, the
underlying connection to the server is actually closed."

 

[hiriumi]

Yes I understand all you said. My point was that it was not happening
in .NET Framework 1.1 somehow, but with .NET Framework 2.0, it is
happening. Yes, my code was flawed, but again, .NET Framework 1.1
somehow gracefully put the connection back to the pool.

So, what exactly made you think that creating a new connection without
closing previous ones wouldn't create a new physical connection?
Your code was flawed before and it probably working due to some strange
reason.
Connection has to be closed before it gets out of scope or even better, asap
it isn't required anymore. BTW the same rule is valid for all classes that
implements IDisposable
Perhaps by reading help file you would know this without loosing time...

"If the SqlConnection goes out of scope, it remains open. Therefore, you
must explicitly close the connection by calling Close or Dispose. Close and
Dispose are functionally equivalent. If the connection pooling value Pooling
is set to true or yes, the underlying connection is returned back to the
connection pool. On the other hand, if Pooling is set to false or no, the
underlying connection to the server is actually closed."

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & developmentwww.rthand.com
Blog:http://cs.rthand.com/blogs/blog_with_righthand/

This never happened before in .NET Framework 1.0 nor .NET Framework
1.1, but connection leak happens if you don't close the connection when
you use SqlTransaction. I would like to share this information with the
MS dev community.

We had this issue of SQL Server performing very poorly while running
our application. My colleague found out that connection leak was
happening whenever the .NET code executed transactions. So I created a
small console program that does a transaction like the following.

Imports System.Data
Imports System.Data.SqlClient

Module Module1

Sub Main()

Begin:
Dim Conn As SqlConnection = GetConnection()
Conn.Open()
Dim Trans As SqlTransaction =
Conn.BeginTransaction(IsolationLevel.ReadUncommitted)

Dim Cmd As New SqlCommand("tblTest_ins", Trans.Connection, Trans)
Cmd.CommandType = CommandType.StoredProcedure

For i As Integer = 1 To 50
Console.WriteLine("Executing stored proc. (" & i.ToString() & ")")
Dim parTestCol As New SqlParameter()
With parTestCol
.ParameterName = "@TestCol"
.Direction = ParameterDirection.Input
.SqlDbType = SqlDbType.NVarChar
.Size = 50
.SqlValue = "TestValue " & DateTime.Now.ToString()
End With



Console.WriteLine("Execution Completed")

Dim Entry As ConsoleKeyInfo = Console.ReadKey()
If Entry.Key = ConsoleKey.Y Then
GoTo Begin
Else
Return
End If
End Sub

Private Function GetConnection() As
System.Data.SqlClient.SqlConnection
Dim Conn As New
SqlConnection("server=(local);database=Test;Pooling=true;user
id=sa;password=whatever;Application Name=HelloConnPool;connection
reset=true;")
Return Conn
End Function

End Module

I ran this code many times, and connection leak was happening. So I
added Conn.Close() right after Trans.Commit(), then the leak was gone.
Well, I could have done Trans.Connection.Close(), but the thing was
that right after the transaction was committed, Connection property was
null. So as we have a data layer that doesn't expose the underlying
connection, we had to define a variable as SqlConnection and hold onto
the reference to the connection from the transaction and the close it
after commit.

I hope I explained this issue well, but this never happened in .NET
Framework 1.1. We converted our code from 1.1 to 2.0, and didn't change
a thing, but this issue came out. I hope this will help people who
experience the same kind of issue. If you have any question, please
just post it here.- Hide quoted text -- Show quoted text -