SQL Compact Edition database corruption

Question

Hi,

Our product is using MS SQL Compact Edition on a Windows machine (laptop). It's basically a metadata index for files we have on the filesystem. Recently we have seen databases getting corrupted.

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

I was somewhat shocked that was at all possible. It was my expectation that the database would stay coherent whatever the circumstances.

Of course we are doing something wrong. Things we have checked so far are:

• Use of only one db connection per thread
• specify the maximum size when opening the database

The database is accessed only by one application, a .net based windows service. Are there other gotcha's?

Answer 1

You might want to consider adding the flush interval property to your SqlCeConnection object's connection string. From MSDN:

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

You may also want to consider creating code to attempt to repair your database. No guarantees, but worth a try.