Critical sync framework issue

Question

Hi,

 

I am facing a very critical issue of sync framework. Sometimes it gives error in synchronization. Error message shows following message:

 

Unable to enumerate changes at the DbServerSyncProvider for table ‘User’ in synchronization group 'IOOfflineProfileToolSyncTableSyncGroup'.

 

Inner Exception :

System.Data.SqlClient.SqlException: SQL Server Change Tracking has cleaned up tracking information for table 'dbo.[User]'. To recover from this error, the client must reinitialize its local database and try again

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

 

This issues comes randomly. I have visited so many links related to sync issues, the only solution I got remove the old .sdf (Local Database) file and create new one, but this solution will not work on client side because client cannot remove .sdf file every time.

 

I am using WPF with sync framework.

 

Thanks in advance

 

Saurav

Answer 1

have you tried increasing the retention period for Change Tracking to a value long enough to hold change tracking metadata in between sync intervals?

Answer 2

Hi JuneT, Thanks for your reply, I have set 10 day in retension time period in SQL Server. But my question is, if this kind error occurs how can we recover our unsynced changes? Is it possible to copy unsynced data into new newly created .sdf file ? Thanks Saurav

Answer 3

without the change tracking information, its hard to tell what changes has occured since the last sync. thus the error message itself suggest re-initializing the client.

without filtering, you can find all the rows from the server that's not in the client and assume they are inserts, all rows in the client but not in the server as deletes and if you have a way of telling updates either via timestamps or date fields, check out what has changed. but this is not foolproof.

thus its easier to just reinitialize the client. you dont have to manually delete the SDF file especially if it has tables that are not part of the sync.

when you get the error, you can just set the TableCreationOption to either TruncateExistingOrCreateNewTable or DropExistingOrCreateNewTable so that Sync Fx re-creates and re-initializes the tables in your sync.

if you need to upload changes from your client, you may set the TableCreationOption to UploadExistingOrCreateNewTable to force the unsynched changes locally to be uploaded.

Answer 4

From the error you pasted, the metadata is cleaned up before the client synchronized. Unfortunately in this case, the client has to reinitialize/reset – meaning get a snapshot of data from the server.

If you don’t want to lose the client sides unsent data changes, copy the sdf to another temp database, initialize the client from the server, do a diff between the initialized client and backup sdf you saved – redo those changes on the client and sync.  

Going forward, you should sync more often and also set the retention policy of SQL Change tracking appropriately (taking into account your sync frequency) so that metadata will not get cleaned up before syncing.

 

---

This posting is provided AS IS with no warranties, and confers no rights

Answer 5

Hi Mahesh,

Thanks for your reply.

Can you provide me any link or sample code (c#) , how can i programetically diff between the newly synced .sdf file and backup .sdf file.

I have used follwoing sql commands to set retention policy of my database..

ALTER DATABASE IOOFFLINEACCOUNTPROFILE SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE IOOFFLINEACCOUNTPROFILE SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 10 DAYS, AUTO_CLEARNUP = ON) 

 

Thanks,

Saurav

Answer 6

Hi,

With ADO.Net APIs, you can load all tables in memory and compare all rows and columns. You can SqlCeDataAdapter to load the whole DataSet into memory, or you can use SqlCeDataReader to stream rows one-to-one to control memory usage.

You also can consider some SQL Compact 3rd party tools as well: http://erikej.blogspot.com/2009/04/sql-compact-3rd-party-tools.html

Thanks,

Dong

 

---

This posting is provided AS IS with no warranties, and confers no rights.

Answer 7

doing a diff, you will have to individually inspect each row on each sdf and compare it with the other and see which one is the most recent. easy to do for inserts/updates when you have timestamps or columns to determine the most recent, but then you'll have to figure out how to handle cases where a row exists in your old sdf but is not in the new sdf, is that a new insert in the old sdf that has not been uploaded yet or is it a row  in the old sdf that has been deleted in the server already?)

it will be easier if you use Sync Fx to tag these un-synched changes so you only have to compare a subset of the table during the diff.

For one you can use the LastSentAnchor to determine the changes that has occured since the last sync. these are your unsynched changes.

assuming you get the error in your posting in the ApplyChangesFailed event, you can also use the ApplyChanges event args which has a SyncContext that contains a Dataset property containing the changes its trying to apply.

When you get the error, simply grab that Dataset and persist it somewhere (you can save the dataset to an XML or insert the rows in another temp table)

Now that you know the changes you havent synched, you can reinitialize the SDF and apply these changes afterwards. if you have a conflict resolution policy that the client always prevails, you can simply overwrite the rows in the new SDF with the unsynched changes that you have.

Answer 8

With regard to setting TableCreationOption to fix this. In what context are you doing that? For instance I have a modified version of http://social.microsoft.com/Forums/en-US/0e2dd016-5d14-4cf9-aebc-93b762b42ec7/sql-server-change-tracking-clean-up-tracking-information?forum=syncdevdiscussions that works for when the ApplyChangesFailed is fired server side, however the exception from this thread is thrown directly from SqlExpressClientSyncProvider.GetChanges(). If I catch the DataSyncException, change TableCreationOption and try to Sync again I get the same exception.