Closing Jet Database seems unreliable

[Heinz Kiosk]

I am having difficulty getting Jet databases to close by calling the
DbConnection.Dispose method. The method seems to work, and the
connection state changes to closed, but about 25% of the time at some
level the process is keeping hold of the connection, and not deleting
the ldb file for example. Then when I terminate my process the ldb file
gets deleted... Or sometimes if I open and close a whole series of mdb's
in sequence the ldb's will build up and up then suddenly they'll all get
deleted at once... :-\

If I open my databases exclusively (mode-share exclusive) (for safety as
the app is single user) then if I close one and then reopen it in the
same process I'll often get a message that the database is already
exclusively open.

Suspecting that connection pooling was getting in the way I tried adding
"OLE DB Services= -1" or -4 to the connection string and curiously this
seems to make things worse. It virtually guarantees that the file will
remain open whereas with pooling turned on the closure seems to work
about 75% of the time. In tests I can see no difference from my point of
view between the times when the call works and the times when the call
fails.

Does anyone know a guaranteed way in ADO.NET of killing a connection to Jet?

I want to do this so that my app can (for example) backup or restore the
Jet database from a menu prompt while the app is still running.

Is there any way of spying what is happening at the underlying Jet level?

Thank you for your help,

Tom

 

[Paul Clement]

¤ I am having difficulty getting Jet databases to close by calling the
¤ DbConnection.Dispose method. The method seems to work, and the
¤ connection state changes to closed, but about 25% of the time at some
¤ level the process is keeping hold of the connection, and not deleting
¤ the ldb file for example. Then when I terminate my process the ldb file
¤ gets deleted... Or sometimes if I open and close a whole series of mdb's
¤ in sequence the ldb's will build up and up then suddenly they'll all get
¤ deleted at once... :-\
¤
¤ If I open my databases exclusively (mode-share exclusive) (for safety as
¤ the app is single user) then if I close one and then reopen it in the
¤ same process I'll often get a message that the database is already
¤ exclusively open.
¤
¤ Suspecting that connection pooling was getting in the way I tried adding
¤ "OLE DB Services= -1" or -4 to the connection string and curiously this
¤ seems to make things worse. It virtually guarantees that the file will
¤ remain open whereas with pooling turned on the closure seems to work
¤ about 75% of the time. In tests I can see no difference from my point of
¤ view between the times when the call works and the times when the call
¤ fails.
¤
¤ Does anyone know a guaranteed way in ADO.NET of killing a connection to Jet?
¤
¤ I want to do this so that my app can (for example) backup or restore the
¤ Jet database from a menu prompt while the app is still running.
¤
¤ Is there any way of spying what is happening at the underlying Jet level?

A sample of your code may help so that we can see what you are doing. I'm assuming that you are
calling Close on the Connection object?


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[William \(Bill\) Vaughn]

Could it be that JET has not finished writing the cache to the database?
Depending on how hard it has been pushed, there might be quite a bit of
uncommitted data there.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

 

[Heinz Kiosk]

¤ I am having difficulty getting Jet databases to close by calling the
¤ DbConnection.Dispose method. The method seems to work, and the
¤ connection state changes to closed, but about 25% of the time at some
¤ level the process is keeping hold of the connection, and not deleting
¤ the ldb file for example. Then when I terminate my process the ldb file
¤ gets deleted... Or sometimes if I open and close a whole series of mdb's
¤ in sequence the ldb's will build up and up then suddenly they'll all get
¤ deleted at once... :-\
¤
¤ If I open my databases exclusively (mode-share exclusive) (for safety as
¤ the app is single user) then if I close one and then reopen it in the
¤ same process I'll often get a message that the database is already
¤ exclusively open.
¤
¤ Suspecting that connection pooling was getting in the way I tried adding
¤ "OLE DB Services= -1" or -4 to the connection string and curiously this
¤ seems to make things worse. It virtually guarantees that the file will
¤ remain open whereas with pooling turned on the closure seems to work
¤ about 75% of the time. In tests I can see no difference from my point of
¤ view between the times when the call works and the times when the call
¤ fails.
¤
¤ Does anyone know a guaranteed way in ADO.NET of killing a connection to Jet?
¤
¤ I want to do this so that my app can (for example) backup or restore the
¤ Jet database from a menu prompt while the app is still running.
¤
¤ Is there any way of spying what is happening at the underlying Jet level?

A sample of your code may help so that we can see what you are doing. I'm assuming that you are
calling Close on the Connection object?


Paul
~~~~
Microsoft MVP (Visual Basic)

Thank you for your response. Isolating down to the portion of code that
is sufficient to generate the problem was quite hard, but I've managed
it now... Rather than trying to write down a block of code that
replicates the problem I'll explain the problem that I've narrowed it
down to because maybe you'll be able to tell me that it is just "by
design" and I'll have to live with it....

I'm using the adapter-wrappers that get built by the data-wizard in the
data-sources toolbox and which can be nicely maintained in the graphical
xsd view of the schema. Since partial classes were introduced this
technique is fine for a lot more purposes than it was originally.
Anyway.... if any of the adapter-wrappers attached to the connection (ie
whose connection property is the connection in question) has *not* been
Disposed, then the Jet connection doesn't close reliably when you
Dispose the ADO.NET DbConnection. So at least I have a workaround, which
is to go round my entire codebase making sure that all local adapters
have a "using" on them, and that all member adapters get disposed in
their class's Dispose, and then that all class's that contain a member
adapter get Disposed if they go out of scope... Quite a pain for
something that doesn't feel as if it ought to be necessary but it does
seem to fix the problem.

My guess is that at some underlying level OleDb is maintaining a count
of objects that theoretically might have a hold on the connection and
not allowing the connection to be closed at that low level unless that
count is zero. *But* it doesn't tell you that anything is wrong when you
call the ADO.NET DbConnection.Close, even though deeper down the close
actually failed.

I'm just calling connection.Close() or connection.Dispose(). If before
the close I do something like new MyAdapter.Fill(MyTable) without then
doing a MyAdapter.Dispose() the database won't close.

Kind Regards

Tom

 

[Heinz Kiosk]

Could it be that JET has not finished writing the cache to the database?
Depending on how hard it has been pushed, there might be quite a bit of
uncommitted data there.

No, it does this even if I've done little or nothing in terms of writing.

I've found out a bit more about what causes the problem, basically it is
if any adapters which have the connection as a member haven't been
Disposed. Religiously Disposing them eliminates the problem... which is
annoying, but at least I have a workaround.

Kind Regards

Tom

 

[William \(Bill\) Vaughn]

Interesting. It seems that the JET driver has a connection pool as well--not
a bit surprising since JET connections also take some time to open. Yes,
it's essential that all connections are closed religiously. That would
certainly explain your issues.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[Heinz Kiosk]

Interesting. It seems that the JET driver has a connection pool as well--not
a bit surprising since JET connections also take some time to open. Yes,
it's essential that all connections are closed religiously. That would
certainly explain your issues.

Thank you, but...

Thing is, I only have one connection, and I am closing it. This is a
winforms app that usually runs single-user on one local PC so connection
pooling and scalability is not an issue and I'm happy to turn it off.
Yet bizarrely turning off connection pooling (OLE DB Services = -1 in
the connection string) actually makes the problem worse for some reason.

I have lots of adapters that reference the connection, and unless I
dispose of them the connection.Dispose() call reports that it has closed
successfully, but doesn't actually close the underlying JET connection.
I don't understand why I have to dispose the adapters first when they
simply reference the same connection object.

Kind Regards,

Tom

 

[William \(Bill\) Vaughn]

Nope, this one has me stumped.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[Earl]

I have been experiencing a similar problem with SQLExpress. Despite
religiously closing the connections, I seem to run out of connections when
starting and re-starting my app repeatedly within a short period of time.
Seems odd to have to Dispose(), but I suspect this is one of the vagaries of
the GC.

 

[Heinz Kiosk]

Nope, this one has me stumped.

OK! I found more problems, then I found a fix.

I found that even if I religiously Dispose of all the adapters I'd
*still* sometimes (perhaps one time in 20 now) get a problem where the
database didn't close at the underlying level when I wanted it to.
Thinking that maybe I was missing an adapter dispose I even resorted to
writing a static class that maintained a list of open adapters that
hadn't yet been disposed. THis worked fine, showed no undisposed
adapters, yet on repeated stress testing I'd still get occasional failures.

So I decided to try a different tack. I wondered if maybe the people who
wrote the ADO.NET oledb jet interface had missed something important out
of the Dispose handling on the connection. Immediately after the
connection.Dispose() call I added the following:

GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();

What gave me the inspiration to do this was the observation that
terminating my app (which presumably calls the above sequence or
something like it) *always* successfully closes the oledb connection.

I don't know if 3 calls were necessary, maybe I could get away with just
the 1 Collect call (har de har), BUT now my app seems to close the
database instantly, every time ) , and I'm not inclined to fiddle with
those calls to get the last 0.001% of performance out of the situation
where a user wants to backup their jet database.

The Dispose just isn't doing everything that is necessary. I suspect I
could have missed out on all the Disposing of the adapaters if I'd
thought of the above attempt at a solution first.

Earl, maybe you could try the above too.

Kind Regards,

Tom

 

[Heinz Kiosk]

Earl, I think I have a solution (in my case at least), which is to force
a garbage collection straight after the connection.close() call, please
see my response to Bill.

Kind Regards,

Tom

 

[William \(Bill\) Vaughn]

That sounds like a bug. Get up on Connect and log it with all of these
details. I do so little work with JET nowadays that these issues don't come
across my plate that often.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------