[dba-VB] Unspecified Error

Hi John --

Did you consistenly use  the following code block when working with MS SQL
Connections?

using (SqlConnection cnn = new
SqlConnection("{{mySQLBackendConnectionStringHere}}"))
 {
        cnn.Open();
        ...
}

When such block is used then Dispose is called "automagically" AFAIK: I have
developed a large ASP.NET application with MS SQL backend - and I have never
had SQL connections leaks with it

Thank you.

--
Shamil
 
-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: 29 ??????? 2010 ?. 18:23
To: Discussion concerning Visual Basic and related programming issues.
Subject: Re: [dba-VB] Unspecified Error

Shamil,

When I say "connection leak", the leak wasn't permanent.  IOW I would
eventually get them back, but not until GC finally cleaned up the
dereferenced class instances.  It seems that there is a finite and
surprisingly small quantity of SQL Server connections available.

My problem was that I would instantiate a class.  Inside of that class I
would dimension a connection, open it, use it, and close it. The problem was
that I would terminate the class and dereference it, so that the GC could
clean it up.  However the GC cleans up when it determines that it needs
*memory*.  It knows nothing about external objects such as connections.

Until the GC cleaned up the class the connection object was still referenced
and the connection object's dispose is not called until GC cleans up.
Whenever the GC finally cleaned up the class, then the dispose of the
connection was finally being called.

Apparently because I have a lot of memory, GC gets "lazy" and doesn't
cleanup all that often.

I was getting all kinds of bizarre behavior around working with SQL Server.
Once I researched the problem and created a dispose in every class that used
connections (which called the dispose of the connection), and manually
called my class' dispose before dereferencing the class that used the
connection, then my "connection leak" went away along with all the bizarre
behavior.

True story, really happened, really went away when I did this.

I am not saying this is necessarily your problem.

John W. Colby
www.ColbyConsulting.com

On 10/29/2010 9:59 AM, Shamil Salakhetdinov wrote:
> Hi John --
>
> Thank you for your reply.
>
> I guess "connections leaking" happens in my case because of the usage 
> of IN clause and because of bug in OleDbProvider happening in that
context:
> "automatic connection" opened somewhere inside OleDbProvider isn't 
> getting closed - something like that....
>
> In general when you use:
>
> using (OleDbConnection cnn = new
> OleDbConnection("{{mymdbConnectionStringHere}}"))
>   {
>        cnn.Open();
>        ...
> }
>
> then there is no "connections leaking" - GC works well.
> The same is true for SQL Server data provider also.
>
> Thank you.
>
> --
> Shamil
>
> -----Original Message-----
> From: dba-vb-bounces at databaseadvisors.com
> [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: 29 ??????? 2010 ?. 14:31
> To: Discussion concerning Visual Basic and related programming issues.
> Subject: Re: [dba-VB] Unspecified Error
>
> I've never programmed against an MDB.
>
> Are you opening and closing the connection repeatedly?  I did have an 
> issue where I was "leaking"
> connections (kinda) and ran out of connections to SQL Server.  
> Basically the garbage collector runs when it thinks it needs to.  If 
> you don't force a cleanup of non-native (to .Net) objects such as SQL 
> Server connections, then the GC doesn't know it needs to clean up and 
> doesn't do so.  I had to build a dispose method for my classes and 
> intentionally call that dispose method as the class was closed, which 
> forced the GC to release the non-native objects immediately instead of
whenever it decided to.
>
> John W. Colby
> www.ColbyConsulting.com
_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com