resolving corruption in SQL Server Compact Edition database files

Question

This is not a query. Its a summary of our solution to get around the problem of corruption in SQL Compact Database files with (almost) definite success. SQLCE Corruption is a very common problem. We've received tremendous help from earlier posts in StackOverflow, and hence this post.

Our product is a 3-tier architecture with the server running as a Windows Service connected to Rich Clients through .Net Remoting. Our product uses SQLCE since 2006. We have moved from v3.1, to v3.5 and now v4.0. We've a custom OR-Mapping tool for some very specific requirements. We've faced limited problems with v3.1, we've faced more with v3.5 and v4.0.

Initially with v3.5, we implemented SqlCeEngine.Repair. But it only drops the corrupted data, and tries to recreate a stable db. We found that Foreign Keys of affected tables went missing. We had to do away with this immediately. We started notifying users about db corruption, and restore the last backup. This only provided a temporary relief; the problem of corruptions still stood.

This year, we adopted v4.0. However, our application also introduced several new features which tremendously increased the number of database calls. v4.0 began well, but started giving problems when the software usage increased. Corruptions happening while the application was running caused neither by Windows crashing, abnormal shutdowns or disk issues. The database just corrupted.

The next post covers the solution that we devised for this problem:

Answer 1

[Separating the query and the solution]

Here goes how we solved the problem:

A) Closing/Disposing Connection/Command/Transaction objects: We ensured that there are no unused, unclosed connection, transaction or command objects. Our ORM tool used to create new objects after calling commit on the transaction, which were lying idle in some cases. This pretty much reduced the number of corruptions by 50%.

B) Disabling Auto-Shrink: The only procedure occurring in the middle of an application run, over which we had no control was Auto-Shrink. We were calling SqlCeEngine.Compact when the application starts. We decided to do away with both Compacting and Auto-Shrinking. And to our surprise, we reduced corruptions by another 48%. It was a shot in the dark, and we could not believe that Auto-Shrinking could have caused such problems. We practically solved the problem with that update.

C) Synchronized Database Transactions: Some database corruptions still happening. With no clear reasons detected, we decided to synchronize database transactions! I know that a lot of database people are not going to like this. I don't like it either. We introduced locks in our middle tier to ensure that only one call is modifying the database at a time. Our largest implementation is 55 clients simultaneously using our system. Synchronizing the database calls hardly resulted in any visible performance delay. Rather, Synchronizing allowed us to implement a timer-driven call to SqlCeEngine.Compact at regular intervals. We knew that Compact was not the culprit, and we felt that Compaction is a necessary call as it reindexes the db (our solution does a lot of inserts and deletes). However, it needs to function exclusively; no database calls when you call Compact. Synchronizing allowed us to control that during an application run. Since we've done that, we've not received a single database corruption problem. Its been more than a month now. From almost 5 clients in a week, to zero in a month.

The basic reasoning which led us to ideas B and C is that SQLCE is an Embedded Database. Corruptions are common to every embedded database solution. A full-scale database solutions works independently supported by a 24x7 db-server managing connections and other tasks. An embedded database system does not have such a support system. The only stage when it is alive is when a connection is opened.

Some more pointers: 1) We implement commit with CommitMode.Immediate, which makes the Flush-Interval property redundant. 2) AutoShrink is set to 100, which disables the procedure completely 3) I've increased the Connection timeout to allow the synchronized database calls to function smoothly. 4) Compact is called when the application starts. In cases where clients do not shutdown their machine at all, we implemented the timer to call Compact every 24-hours.

Hope this post helps solve problems.