Pooled connection error

Similar Threads:

1.connection in connection pool with pooling=false

I'm finding that even when pooling=false, a connection remains in my
connection pool connected to my database (it seems to quite quickly timeout
though - say 30 seconds).

My connection string:

    Initial Catalog=Benji_UnitTest;Data Source=.;Integrated
Security=SSPI;Pooling=false



Using this setting, I would expect that a new connection be created
everytime, and that when the SqlConnection is close()'d (i.e., returned to
the "pool") that the underlying database connection would be closed.

I also tried setting ";Connection Lifetime=1" in order to get the connection
to close quickly (as a worksround); this didn't seem to affect any change.

Am I misunderstanding something?



Thanks,

j

2.Leftover Connections in Connection Pool (connection leak)

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.

3.Trap "connection pool" errors [crosspost from .asp]

"Sean Nolan" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
> We have implemented unhandled error trapping at the application level and
> log these errors to our database. One error, however, the does not get
> trapped is when the connection pool has exceeded the max number of
> connections.
>
> Obviously, we need to find the place(s) in our code where connections are
> not closed correctly (espcially in loops), but I'm wondering if it's
> possible to trap this error and to find out which part of our code (i.e.
> stack trace) caused it.

It it possible.  The general idea is to have a object that will be Garbage
Collected in the same pass as your connection.  Whenever a connection is
opened, store the stack trace of the opening method. And put a finalizer on
that object, and write out a trace entry if the finalizer runs and the
connection is still open.

One way to do this is to have a "wrapper object" for your connection.
But then your app code has to create the wrapper instead of the connection.
I think this is a good thing, since you can implement all the DAAB methods
as instance methods of your wrapper object.  But that's another story.

Assuming you are using SQLServer (or some other connection that has a
StateChanged event), there may be an easier way.

Without a wrapper object, to get an object which will be finalized at the
same time as the connection we can use a "spy" object.

If we have a "spy" object which handles the StateChaned event of the
SQLConnection, and we give the spy object a reference to the connection we
will have what we want.  If 2 objects mutually refer to each other, then
they will always be GC'd at the same time.  The spy refers to the
SQLConnection and since the spy handles an event on teh SQLConnection, the
SQLConnection's delegate list contains a reference to the spy object. Voila!

There follows sample program to do this.

David

Imports System.Data.SqlClient

Class ConnectionFactory
  Private Class ConnectionSpy
    Private con As SqlConnection
    Dim st As StackTrace
    Public Sub New(ByVal con As SqlConnection, ByVal st As StackTrace)
      Me.st = st

      'latch on to the connection
      Me.con = con
      AddHandler con.StateChange, AddressOf StateChange
    End Sub
    Public Sub StateChange(ByVal sender As Object, ByVal args As
System.Data.StateChangeEventArgs)
      If args.CurrentState = ConnectionState.Closed Then
        'detach the spy object and let it float away into space
        GC.SuppressFinalize(Me)
        RemoveHandler con.StateChange, AddressOf StateChange
        con = Nothing
        st = Nothing
      End If
    End Sub
    Protected Overrides Sub Finalize()
      'if we got here then the connection was not closed.
      Trace.WriteLine("WARNING: Open SQLConnection is being Garbage
Collected")
      Trace.WriteLine("The connection was initially opened " & st.ToString)
    End Sub
  End Class

  Public Shared Function OpenConnection(ByVal connect As String) As
SqlConnection
    Dim con As New SqlConnection(connect)
    con.Open()
    Dim st As New StackTrace(True)
    Dim sl As New ConnectionSpy(con, st)

    Return con
  End Function

End Class


Module Module1

  Sub Main()
    'pipe trace output to the console
    'in your app this would go to a trace file
    System.Diagnostics.Trace.Listeners.Add(New
TextWriterTraceListener(System.Console.Out))
    Dim connect As String = "..."


    Dim c As SqlConnection = ConnectionFactory.OpenConnection(connect)
    c = Nothing '!!the connection was not closed

    c = ConnectionFactory.OpenConnection(connect)
    c.Close() 'this time it was closed
    c = Nothing

    GC.Collect(GC.MaxGeneration)
    GC.WaitForPendingFinalizers()
    'output will show 1 warning


  End Sub

End Module

4."General Network Error" and Connection Pooling

I have a very simple WinForms app, that uses a sqlDataAdapter retrieve data 
into a dataset.
The dataset is set to be the datasource of a DataGrid. If - while editing 
rows in my grid - i lose my network connection (or stop + start my 
sqlserver), i get a "General Network Error" when I call my dataAdapters 
.update method. (...naturally, after reestablishing network connection, that 
is...)

However, if i disable connection pooling, I get no error. Also, with 
connection pooling enabled, if I repeatedly call update, eventually it 
succeeds on the third attempt.

Anyone who can smarten me up?

J. Jespersen
Denmark

5.Connection Pooling Error

Hi Mike,

Do you have the call stack for the exception? That would really help us
identify the problem.

Thanks,

-- 
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.


"Mike Sarbu" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
> Hi,
>
> I have a Windows.Forms application (framework 1.1) connected to a SQL
Server
> 2000 Enterprise. I was using connection pooling and the users were getting
> connection pooling errors (25 users, 100 connections in pool). The error
was
> very random. The exact text of the error is "A connection pooling error
has
> occurred". It was getting annoying so I disabled connection pooling, but
> they still get the same error.
>
> Any idea?
>
> TIA,
>
> Mike
>
>

6. SQL Server connection Pool error in Debug

7. urgent: connection pooling error

8. A connection pooling error has occurred