FIX: It takes longer time than flush interval to flush committed transactions in SQL Server Compact 4.0

Symptoms

Assume that you have specified the flush interval in the maximum number of seconds in the connection string before committed transactions are flushed to disk in Microsoft SQL Server Compact 4.0. In this situation, the committed transactions may take much longer time than the flush interval to be flushed to disk or may not even be flushed to disk. Additionally, data loss occurs if there is an abnormal program termination.

Resolution

The hotfix that resolves this problem is included in an on-demand hotfix update package for SQL Server Compact 4.0 Service Pack 1. 

For more information about how to obtain this on-demand hotfix update package for SQL Server Compact 4.0, click the following article number to view the article in the Microsoft Knowledge Base: 
2960153  On-demand hotfix update package for SQL Server Compact 4.0 Service Pack 1

Workaround

To work around this issue, use CommitMode.Immediate to flush the changes immediately instead of relying on the flush interval. The following sample code demonstrates this technique:
conn.ConnectionString = "Data Source = c:\temp\SalesData.sdf; "
conn.Open()
Dim tx As SqlCeTransaction
tx = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted)
com = conn.CreateCommand()
com.Transaction = tx
Dim a As Integer
a = 101
While (a <= 1000)
com.CommandText = "insert into Orders values (" + Convert.ToString(a) + ", 'best', 200)"
com.ExecuteNonQuery()
a = a + 1
End While
tx.Commit(CommitMode.Immediate)

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Properties

Article ID: 2979868 - Last Review: Jul 25, 2014 - Revision: 1

Microsoft SQL Server Compact 4.0

Feedback