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

Similar Threads:

1.Trap "connection pool" errors

"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

2.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

3.SqlClient Connections created don't get reused in the pool and cause error in ASP.NET Page

I have a ASP.NET app which I have recently converted from OleDB to 
SqlClient, however when I Get a connection, use and close it, It stays 
open (Process Info in Sql Server Enterprise Manager say the connection 
is sleeping) and very soon I hit the currently defined 100 connections 
limit?

Why is this the code I use is as follows:-

using (SqlCommand innerConnect = DBHelper.GetConnection())
{
	using (SqlCommand innerCmd = new SqlCommand("StoredProcName", 
innerConnect))
	{
		innerCmd.CommandType = CommandTyle.StoredProcedure;
		innerCmd.Para,eter.AddWithValue("@Parm1", Variable1);
		
		SqlDataReader innnerRS = innerCmd.ExecuteReader();
		if (innerRS.Read())
		{
			lblNumber1.Text = innerRS.GetString(innerRS.GetOrdinal("RetCol1"));
		}
		innerRS.Close();
	}
}

The DBHelper class contains a static method as follows: -

public static SqlConnection GetConnection()
{
	SqlConnection conn = new 
SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr1"].ConnectionString);
	conn.Open();
	return conn;
}

4.IIS ASP.NET 2.0 Not creating a connection pool nor re-using connections

I need some help!

I have a webapp in ASP.NET 2 running on 2003 R2 that doesn't seem to
want to bring up a connection pool.

When I run a trace on the DB server I can see Audit Logoff and Audit
Logon between query batches, which leads me to believe that this is
occurring.

This is a replacement platform for our current live systems, so I have
just tried to build everything as similarly as possible throughout.

The main difference is that the DB server is now SQL2005 SP2 whereas
before it was SQL2000. I haven't read anything indicating that this
would be the cause, but I am very open to ideas.

The connection string I am using is:

Application Name='Live';pooling=true;Min Pool Size=1;Max Pool
Size=1000;database=DB1;User ID=USER ;Password=PASSWORD;Server=live-
db1;


I cannot find any way to verify information on connection pooling
working, other than by using SP_WHO - which does appear to show an
idle connection or two from the webapp, but like I say, I can see the
connections being torn down and back up every batch from the profiler
trace.

The reason I'm investigating this is that the application is serving
pages incredibly slowly when they have to hit the database, but I've
tried running the query batches directly through MSMS on both the DB
server and App servers, and they are very fast - much faster than when
run via .NET. This connection latancy is the only thing I can put it
down to at the moment.

I'd welcome any insight.


Jon

5.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.

6. SQL Server Error Trapping in ASP/ASP.NET

7. Pooled connection error

8. "General Network Error" and Connection Pooling