Can only download incremental changes once: "SQL Server cleaned up tracking information" RRS feed

  • Question

  • I have an application which uses Sync Framework to synchronize a central SQL Server 2008 R2 database with a constellation of SQL CE 3.5 databases on several different clients. The central server can be accessed from remote clients using a WCF service. All tables are configured to download incremental changes from the central database. Incremental changes are calculated using SQL Server Change Tracking.

    I have run into an odd error. If I initialize an empty SQL CE database using the WCF service and Sync Framework, everything works fine. If, just a few seconds later, I try a second synchronization, it fails with the message "SQL Server Change Tracking has cleaned up tracking information for table." My central database is set up to clean change tracking information only every 365 days, so I can't imagine that a cleanup would have occurred in the few seconds between the primary and secondary synchronization.

    Apparently it is possible to reinitialize change tracking for a table by setting the ReceivedAnchor column of the __syncArticles table to NULL. (This is much less destructive than creating the client database from scratch.) After going through several iterations of this, I discovered that of the 50+ tables involved in sync, only 20 of them caused the "cleaned up tracking information" error. Once the received anchor for these tables is set to NULL, synchronization works fine. If you sync again, though, same error as before. "Cleaned up tracking information."

    Tuesday, October 18, 2011 5:58 PM


  • have you tried changing the syncNewReceivedAnchor as an Output parameter?
    • Marked as answer by Karl Dickman Friday, October 28, 2011 3:21 PM
    Friday, October 28, 2011 12:41 PM

All replies

  • can you run SQL Profiler and look at the selectincrementalchanges commands?

    then try to execute the commands you captured from the profiler and see if they actually pick up changes.


    Wednesday, October 19, 2011 1:16 AM
  • Here is the first SQL statement that causes offense:

    USE WindEnergy
    DECLARE @sync_initialized bit
    DECLARE @sync_last_received_anchor bigint
    DECLARE @sync_new_received_anchor bigint
    DECLARE @sync_client_id_binary varbinary(16)
    SET @sync_initialized=1
    SET @sync_last_received_anchor=0
    SET @sync_new_received_anchor=0
    SET @sync_client_id_binary=0x554DB1B1C8047845A081451377315485
    IF @sync_initialized = 0
    	SELECT [Completenesses].[CompletenessID], [Completeness]
    		FROM [Completenesses]
    	SELECT [Completenesses].[CompletenessID], [Completeness]
    		FROM [Completenesses]
    			JOIN CHANGETABLE(CHANGES [Completenesses], @sync_last_received_anchor) CT
    				ON CT.[CompletenessID] = [Completenesses].[CompletenessID]
    			AND CT.SYS_CHANGE_CREATION_VERSION  <= @sync_new_received_anchor
    			OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary));
    	IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'[Completenesses]')) > @sync_last_received_anchor
    		RAISERROR (N'SQL Server change tracking has cleaned up tracking information for table ''%s''. To recover from this error, the client must reinitialize its local database and try to synchronize again.',16,3,N'[Completenesses]')

    Note: the actual SQL statement uses sp_executesql, but I've refactored it to something a little more comprehensible.

    Now, there are four tables which are synchronized before this one. All use identical statements save for the name of the tables. All set both the sync_last_received_anchor and sync_new_received_anchor to zero. However, those first tables select their incremental inserts just fine.

    Furthermore, I can tell you from looking at my SQL CE database that the ReceivedAnchor is 0x0001000000FFFFFFFF010000000000000004010000000C53797374656D2E496E74333201000000076D5F76616C75650008000000000B for the table in question. Certainly not zero.

    Wednesday, October 19, 2011 7:35 PM
  • By the way, CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'[Completenesses]')) is 1761. For the tables that synchronize before Completenesses, the equivalent call returns zero.

    So the SQL Server side is behaving exactly as one would expect. No surprises there.

    The real question is, why does the client insist on claiming that it's last received anchor was zero?

    Wednesday, October 19, 2011 7:38 PM
  • did you just use the Local Database Cache wizard to generate the sync codes?

    for the problematic tables, were they added to the database at the same time as the working tables?\

    if you can, can you try disabling SQL Change Tracking and re-enabling it?

    Thursday, October 20, 2011 1:30 AM
  • Sorry for the tardy response. I posted a reply a week ago, or rather I thought I did.

    I did not use the Local Database Cache wizard. All the SQL statements are generated by the SqlServerSyncAdapterBuilder class. Not that this makes any difference; the SQL generated by either of these two methods is identical as far as I can see.

    The problematic tables were all added to the database at the same time as the working tables. In fact, all of them predate the introduction of change tracking by months or years.

    Disabling and re-enabling change tracking has been tried several times on several tables to no effect.

    Wednesday, October 26, 2011 5:24 PM
  • some other things you might want to look at:

    - was TRUNCATE ran on the tables after Change Tracking was enabled?

    - not sure if there are any custom code written for the setting and getting the LastReceivedAnchor. this anchor should not have a zero value after the initial sync

    - check the code/sql retrieving the sync_new_received_anchor, this should not be zero.

    Thursday, October 27, 2011 1:59 AM
  • Here is the code I have for getting the new received anchor:

    string syncNewReceivedAnchor = "@sync_new_received_anchor";
    string commandText = string.Format("SELECT {0} = CHANGE_TRACKING_CURRENT_VERSION()", syncNewReceivedAnchor);
    IDbCommand selectNewAnchorCommand = Connection.CreateCommand(commandText, CommandType.Text);
    selectNewAnchorCommand.AddParameter(syncNewReceivedAnchor, SqlDbType.BigInt);
    This was borrowed from the code generated by the Local Database Cache wizard.

    Thursday, October 27, 2011 4:34 PM
  • have you tried changing the syncNewReceivedAnchor as an Output parameter?
    • Marked as answer by Karl Dickman Friday, October 28, 2011 3:21 PM
    Friday, October 28, 2011 12:41 PM
  • That's seems to have fixed the problem. As I was moving code around I must have dropped the output parameter direction.
    Friday, October 28, 2011 3:20 PM