SQL Compact Edition Database Corruption

Question

Our product uses MS SQL Compact Edition on a Windows computer (laptop). This is basically a metadata index for the files we have in the file system. We recently discovered that databases are getting corrupted.

This happens when the machine is very busy moving files around and needs to execute a tiny bit of database changes at the same time.

I was somewhat shocked that this was possible. I expected the database to remain unchanged in all circumstances.

Of course, we are doing something wrong. So far we have checked:

• Using only one db connection per stream
• specify the maximum size when opening the database

Access to the database is provided by only one application based on Windows. Are there any other matches?

Answer 1

You might want to add a flush interval property to your SqlCeConnection object connection string. From MSDN:

stripping interval - or - ssce: flushing interval → Indicates the time interval (in seconds) before all committed transactions are flushed to disk. If not specified, the default value is 10.

You might also consider creating code to try to restore your database . No guarantees, but worth a try.