title: SQL Server CE 3.5 Performance issue
body: <p>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. </p>
  
  <p>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. </p>
  
  <blockquote>
    <p>__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.<br>
    at System.Data.SqlServerCe.SqlCeChangeTracking.ProcessResults(Int32 hr)<br>
    at System.Data.SqlServerCe.SqlCeChangeTracking.CheckIfDataIsPresentInTombstone(Int64 retentionValue, PurgeType pType)<br>
    at System.Data.SqlServerCe.SqlCeChangeTracking.PurgeTransactionSequenceData(PurgeType pType, Int64 retentionValue)<br>
    at Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.SetTableSentAnchor(String tableName, SyncAnchor anchor)</p>
  </blockquote>
  
  <p>Additionally we have seen the setting for RetentionInDays set as 60, as below. Is there any impact of this?</p>
  
  <pre><code>[ConfigurationProperty("clientRetentionInDays", DefaultValue = 60, IsRequired = false)]**
  </code></pre>
  
  <p>Currently there is no purge method in the code. So we tried to use <code>PurgeTombstoneTableData</code> method of the <code>SqlCeChangeTracking</code> class to cleanup the <code>Tombstonetable</code> data in the system table (<code>_sysOCSDeletedRows</code>). But we are unsuccessful, as we get the exception </p>
  
  <blockquote>
    <p>The change tracking operations are not permitted on the system tables. [ Table name = <strong>__sysOCSDeletedRows</strong> ]</p>
  </blockquote>
  
  <p>In addition to the above we have also tried <code>SqlSyncStoreMetadataCleanup.PerformCleanup</code> method similar to the code snippet below. But still we receive the error The change tracking operations are not permitted on the system tables.</p>
  
  <p><a href="https://msdn.microsoft.com/en-us/library/ee617373(v=SQL.110).aspx" rel="nofollow">https://msdn.microsoft.com/en-us/library/ee617373(v=SQL.110).aspx</a></p>
  
  <pre><code>SqlSyncStoreMetadataCleanup metadataCleanup = new SqlSyncStoreMetadataCleanup(serverConn);
  bool cleanupSuccessful;
  metadataCleanup.RetentionInDays = 7;
  cleanupSuccessful = metadataCleanup.PerformCleanup();
  </code></pre>
  
  <p><strong>Questions:</strong></p>
  
  <ul>
  <li><p>How can we handle this exception while cleaning up the Tombstonetable entries?</p></li>
  <li><p>What are the other tables where Tombstonetable are stored? </p></li>
  <li><p>How do we overcome this performance issue with SDF after 60 days of regen?</p></li>
  <li><p>What are the other best practices can be followed?</p></li>
  <li><p>Any hint on how to successfully cleanup Metadata (PerformCleanup) and what are the tables to be validated after cleanup?</p></li>
  </ul>
  
  <p>Thanks in advance.</p>
  
  <p>Regards,
  Sundar.</p>