DB Corruption

  • Thread starter Thread starter MDB
  • Start date Start date
M

MDB

Due to memory issues, I recently changed my application to open,close and
dispose the db connection on the "fly" rather than using a shared
connection. According to the article in the link below, this is
recommended. Now that I have done this, A lot of my devices in the field
are getting corrupted databases. Has anyone else ran into this or am I
better off going back to a shared connection?

TIA


http://blogs.msdn.com/raffael/archive/2008/02/21/netcf-memory-leak-now-what.aspx
 
I'd be surprised if a shared connection would solve whatever is causing your
data corruption issue. What version of SQL CE are you using? Is the data
located on a storage card? Are you still having out of memory issues?

Ginny Caughey
Device Application Development MVP
 
Weare still using SqlCe version 2.0, which you know is a "single trick pony"
when it comes to concurrent connections. We have Always had the database on
an SD card, and we have always do an Open, Close and Dispose of the
connection on each database access. In three years we have had only ONE
caase of a corupted database. Performance has been quite acceptable for our
purpose.

Our application is the only application running (Kiosk mode) so we do have
not had any memory issues to date. We do take precautations before we open
a connection however. We have a WaitForSDMount Function that insures that
the SD card is mounted before we attempt to open a connection.

Rdub
 
I am using 3.0 which I recently switched to and no, I am no longer running
into memory errors. Also, the data is not on the sd card however, I do make
a copy to the SD card throughout the day. I believe I read somewhere that
you should not consistently read/write to the SD card because it can cause
them to become corrupted so I only copy the DB over after key points in the
application.


I thing I did notice is that my db compaction is no longer working, due to
memory issues, I didn't include all of the sqlce cab files and just included
the sqlcompact30.dll. I thought just including this dll would work since it
worked on my test devices however, out in the field it didn't. Do you
believe that since my compactions are failing and with that, the dbs never
really being cleaned up, could be causing the corruption?

Thanks
 
Are you doing a bunch of read / writes to the DB? My application is very
data intensive and at one time I was going to place the DB on the SD card
however I read that you shouldn't do that since SD cards are only good for
so many reads/writes and that they will go bad quickly.
 
I don't know if the failure of compaction is related to your data
corruption, but you'll certainly want to try and troubleshoot that issue. As
far as I know the compact dll is the only addtitional dll you need for
compaction. Sometimes there are issues with compaction if it is attempted
after your app has been running for a while (instead of immedicately at the
start of the app). The reason is that there is no more space to load the
dlls needed for compaction, but instatiating a type used by compaction, even
if you don't call a method on it right away, should solve the issue if that
is what's happening.

One other question - are you accessing the sdf file from more than one
thread? If so, does each thread have its own connection?

Ginny
 
Thats what I originally thought and is why when I updated my devices I only
included the compaction dll and the sqlce30.ppc.... cab file. Once I
figured out the devices were not able to compact which is done only at start
up, I added the sqlce30.dev and sqlce30.repl files and it started working
(although I haven't sent it out to all of my devices, at least it started
working again in my test group)?

As for multiple threads, yes, I have two threads, the main thread and a
messaging thread. All access to the db is done on the fly and no
connections are shared.

When this started happening, on a couple of the devices, I ran a verify and
repair, and they were fixed. The ones I am seeing now can't even be
repaired and when I call the repair function, it is returning an exception
saying the operation completed sucessfully?
 
Sorry, the last statement is / question was posted in sqlserver.ce group
which you responded to already.


Thanks
 
If you install the sqlce cab file, that should include all the dlls, so I'm
a little confused about what's going on with your devices. The repl dll is
used for replication, so probably you don't need that (unless of course you
are doing replication.)

One other thing you might try is upgrading to SqlCe 3.5 and see if that
changes anything.

Ginny
 
No, I am not doing any replication so I will remove it. I am going to see
if adding this file helps anything and if the compaction starts working. If
not, I will have to look into upgradeing to 3.5 like you mentioned.
 
Our app does data collection using an integrated barcode scanner. We add a
new record after each scan, and edit existing records as the user requires.
We also collect Audio Wav and Picture Jpg files with the device and save
those on the SD card too. There are times we could hit the database with a
new record every second or two, sometimes it's hours between scans.

I also read that the SD cards do wear out after so many read writes. All i
can say is that it must be a pretty high number, as we still have some units
with 3 year old ultra cheap 128Mb SD cards. I suspect we "loose" more of
em' than go bad, especially the faster 512Mb and 1Gb ones. :-)

Rdub
 
Thank you for that information, I have been wanting to place the db on the
cards but was worried about what I read. I think I am going to give that a
try once I get the corruption under control. Its nice hearing someone has
done that and hasn't had any problems with it.
 
I'm interested in the technique for making a copy of the database. Is it safe
(or even possible) to copy the database while the connection is open.

My application is currently fairly simple. I'm doing all the things the
domumentation I now read says not to do. Dataset, tableadapters, shared
connection kept open. At the moment I don't have any memory issues, but as
the application and database gets bigger .....

Thanks
 
SEDGE,

You can use the SqlCeEngine.Compact method to copy a database to a different
file.I'm not sure if you can do this while other connections are open as the
docs don't say. I'm not having issues just using the database on the SD
card, so you might try that. It would certainly be simpler than what you're
doing now.

Ginny
 
Back
Top