Leftover Connections in Connection Pool (connection leak)
- From: hiriumi@xxxxxxxxx
- Date: 22 Jan 2007 17:37:12 -0800
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.
.
- Follow-Ups:
- Re: Leftover Connections in Connection Pool (connection leak)
- From: Miha Markic [MVP C#]
- Re: Leftover Connections in Connection Pool (connection leak)
- Prev by Date: Re: Typed dataset: Time only data in SQL dateTime Field
- Next by Date: MS SQL 2005 Configuration ???
- Previous by thread: Queries to an Access db from vs2005
- Next by thread: Re: Leftover Connections in Connection Pool (connection leak)
- Index(es):
Relevant Pages
|
