[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