0

We are currently using SQL Server CE 3.5 with many different bidirectional sync dbs (Syncframework 2.1) with server DBs, and .net 3.5 windows based WPF application. But down the line the performance of the application degrades and we regularly need to regenerate the new local SDF for users. And by that way we see there is visible improvement in performance and after 60 days or so, the same happens.

When we enabled the trace in the configuration, we noticed below exception is continuously logged in the trace file. In our code we see only the method call 'SetTableSentAnchor', how are the other methods invoked subsequently? How can we handle this exception.

__sysTxCommitSequence: System.InvalidOperationException: The retention clean-up sequence is invalid. For the specified input range, clean up the tombstone table prior to cleaning up the transaction table history.
at System.Data.SqlServerCe.SqlCeChangeTracking.ProcessResults(Int32 hr)
at System.Data.SqlServerCe.SqlCeChangeTracking.CheckIfDataIsPresentInTombstone(Int64 retentionValue, PurgeType pType)
at System.Data.SqlServerCe.SqlCeChangeTracking.PurgeTransactionSequenceData(PurgeType pType, Int64 retentionValue)
at Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.SetTableSentAnchor(String tableName, SyncAnchor anchor)

Additionally we have seen the setting for RetentionInDays set as 60, as below. Is there any impact of this?

[ConfigurationProperty("clientRetentionInDays", DefaultValue = 60, IsRequired = false)]**

Currently there is no purge method in the code. So we tried to use PurgeTombstoneTableData method of the SqlCeChangeTracking class to cleanup the Tombstonetable data in the system table (_sysOCSDeletedRows). But we are unsuccessful, as we get the exception

The change tracking operations are not permitted on the system tables. [ Table name = __sysOCSDeletedRows ]

In addition to the above we have also tried SqlSyncStoreMetadataCleanup.PerformCleanup method similar to the code snippet below. But still we receive the error The change tracking operations are not permitted on the system tables.

https://msdn.microsoft.com/en-us/library/ee617373(v=SQL.110).aspx

SqlSyncStoreMetadataCleanup metadataCleanup = new SqlSyncStoreMetadataCleanup(serverConn);
bool cleanupSuccessful;
metadataCleanup.RetentionInDays = 7;
cleanupSuccessful = metadataCleanup.PerformCleanup();

Questions:

  • How can we handle this exception while cleaning up the Tombstonetable entries?

  • What are the other tables where Tombstonetable are stored?

  • How do we overcome this performance issue with SDF after 60 days of regen?

  • What are the other best practices can be followed?

  • Any hint on how to successfully cleanup Metadata (PerformCleanup) and what are the tables to be validated after cleanup?

Thanks in advance.

Regards, Sundar.

Sundar
  • 655
  • 2
  • 7
  • 13

1 Answers1

0
  • Don't use SqlCeEngines Cleanup, Repair or Compact. It will mess up your database and your database performance.
  • Only open or generate the database on the target operating system. If you opening a database on Windows CE generated on Windows 10 (we got different Codepages here) it will need to be converted to the target platform, which takes an incredible amount of time. Windows CE and Windows XP are the only platforms where a generaded database can be savely opened.
  • Consider switching to SQLite to avoid all that crap.
Redwolf
  • 540
  • 4
  • 17