none
SQL Server Change Tracking Clean up tracking information

    General discussion

  • This a common Question about Sync Services For Ado.NET and Sql Server Change Tracking :

    The Problem :

    When you synchronize, you can have sometimes this kind of error :

    SQL Server Change Tracking has cleaned up tracking information for table Client. 
    
    
    
    To recover from this error, the client must reinitialize its local database and try again
    
    
    
    
    What the ____ is that !!

    It simply means that your database server has run change tracking cleanup since your client last synchronized thereby causing a situation where your client's database may be missing rows or be otherwise inaccurate.  It detected this because...
    MIN_VALID_VERSION (the current version on the server) is greater than your client's last pulled version.  This means, the last version on the server which is valid after cleanup is from a point AFTER your client last synched.

    This error comes From the T-SQL Statement used for all Synchronization. Here is a sample on a SelectIncrementalSelect command :

    IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.ClientPhoto')) > @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 again',16,3,N'dbo.ClientPhoto') 
    

    Notice the RAISERROR arguments : Class == 16, State == 3


    The Solution :

    This one of the possible solution. I m' not sure this is the best way to achieve a good synchronization, but it works in my case :)
    I have a Third - Tiers solutions : One project for the client application (WPF or Windows CE, whatever) One project for the Server service, exposed by WCF and directly connected to the Database and One project for common Interfaces (project not mandatory)

    The Server Side :

    On the Server Side, we must catch the error from Sql Server (already catched by Sync Services.) and send back this information to the client.
    The first thing to do, is enable the ServiceBehavior to include the Exception in the FaultedException sended back to the client:

    On my class (which implements the interface) i write an specific attribute like this :

        [ServiceBehavior(IncludeExceptionDetailInFaults = true)]
        public partial class DonutSyncSyncService : object, IDonutSyncSyncContract
        {
         .....
        }
    

    Ok, now we must catch the error.
    Remember that :

    1. This error come from Sync Services : It's a SyncException (with a particular SyncErrorNumber : StoreException)
    2. This error is raised by Sql Server so the InnerException must be SqlException
    3. The RAISERROR arguments are : Class == 16, State == 3

    This error could be raised by all the ServerProvider methods (GetChanges, ApplyChanges, GetSchema, GetServerInfo)

    Here is the code for one method : (GetChanges) the code is the same on others methods :

            public virtual SyncContext GetChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession)
            {
                try
                {
                    var tmp = this._serverSyncProvider.GetChanges(groupMetadata, syncSession);
                    return tmp;
                }
                catch (SyncException se)
                {
                    if (se.ErrorNumber == SyncErrorNumber.StoreException)
                    {
                        SqlException sqlExcept = se.InnerException as SqlException;
                        if (sqlExcept != null && sqlExcept.Class == 16 && sqlExcept.State == 3)
                        {
                            throw new ApplicationException("ChangeTrackingCleanedUp", se);
                        }
                    }
                    throw se;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
    

    You can notice that i handle the error and raised an ApplicationException, with a significant Message (ChangeTrackingCleanedUp), that i can easely catch on the client side.

    On the Client Side :


    There is two solutions to correct the problem.

    1. Delete the local database : Wrong way ! If you delete the database, you could loose all your datas, even your "new records, that must be synchronized"
    2. Mark all the table for a complete "Re Sync" : In My Opinion, the good solution

    To mark a client table for a complete synchronization, you must set its ReceivedAnchor to Null. This is the simple way to achieve this :

    sqlCeProvider.SetTableReceivedAnchor(st.TableName, new SyncAnchor());
    

    To make it simplier, i made a new Synchronize method : 

            public SyncStatistics Synchronize(bool reinit)
            {
                if (!reinit)
                    return base.Synchronize();
                try
                {
                    SqlCeClientSyncProvider sqlCeProvider;
                    sqlCeProvider = (SqlCeClientSyncProvider)this.LocalProvider;
    
                    foreach (SyncTable st in this.Configuration.SyncTables)
                    {
                        if (st.SyncDirection != SyncDirection.Snapshot)
                        {                        
                            sqlCeProvider.SetTableReceivedAnchor(st.TableName, new SyncAnchor());
                        }
                    }
                }
                catch (Exception ex)
                {
                }
                return base.Synchronize();
            }
    

    Ok, now we can handle the error during a Synchronization. Here is the code i used in my Client application :

                DonutSyncSyncContractClient proxy = new DonutSyncSyncContractClient("WSHttpBinding_IDonutSyncSyncContract");
                DonutSyncSyncAgent myAgent = new DonutSyncSyncAgent(proxy);
                myAgent.SessionProgress += new EventHandler<Microsoft.Synchronization.SessionProgressEventArgs>(AgentSessionProgress);
                SyncParameter param = new SyncParameter("@OwnerEmployeId", DonutSettings.Default.EmployeId);
                myAgent.Configuration.SyncParameters.Add(param);
    
                try
                {
                    var stats = myAgent.Synchronize(false);
                }
                catch (TargetInvocationException ex)
                {
                    if (ex.InnerException != null && ex.InnerException.Message == "ChangeTrackingCleanedUp")
                    {
                        try
                        {
                            var stats = myAgent.Synchronize(true);
                        }
                        catch
                        {
                        }
                    }
                }
                myAgent.SessionProgress -= new EventHandler<Microsoft.Synchronization.SessionProgressEventArgs>(AgentSessionProgress);
    

    Conclusion

    In this way, the client must do TWO Synchronization, to re-init the client database.
    If you have a better way to do that, please post ypur solution here :)

    And one more time, sorry for my poor English, French guys are not very good english speakers :) 


    Sébastien Pertus. Bewise
    http://www.dotmim.com/blogs/mim/default.aspx

     

    Tuesday, August 11, 2009 2:47 PM

All replies

  • Hi there,
    I making currently making a application where the requirement is to take data offline and after completion syncing it to main server. i am using microsoft sync framework.
    everything was fine, working great, and i was happy, until i was striked by this error.
    if the client/pda is not used for few days, then when next time sync is performed, it throws targetinvocation error. the exception is same as you have mentioned in this article. resetting of the client database can't be a solution since it will have unsaved data.
    i tried using the method you have posted here, but now, strangely for me, it does not throw exception but the synchronization is no longer being performed. the syncing has disappeared.
    please help.

    regards 
    Thursday, January 14, 2010 9:52 AM
  • Hi,

     

     I used the solution mentioned above and it worked. But I am not able to catch the "ChangeTrackingCleanup" exception thrown from the server at the client. The client still receives the CFFault exception which it usually throws in case of such error. Please help to catch the exception so that I can take the action as shown above.

     

    Thank you,

    Divya.


    Divya
    Monday, July 18, 2011 7:27 PM
  • I periodically get the same issue in the Server ApplyChangedFailed event: "SQL Server Change Tracking has cleaned up tracking information for table..." however I can not reliably reproduce in production.

    In unit tests I can reproduce with successful sync followed by running: 

    ALTER TABLE order_details DISABLE CHANGE_TRACKING; ALTER TABLE order_details ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON)

    then next sync will give the issue. For server side I also had to do a ClientUpdateServerUpdate conflict to get the issue to throw.

    I have retention set for 14 days and often get this issue within seconds or minutes after database creation.

    A consequence of the approach above is that all client rows are re-sent to server causing ClientInsertServerInsert conflict.

    For anyone concerned here is the basic C# version of the client code for SqlExpressClientSyncProvider. Also have to modify SetTableReceivedAnchor() to set anchor parameter to DBNull.Value.

    try {
    	syncStatistics = syncAgent.Synchronize();
    } catch (FaultException exception) {
    	if (exception.Message.Contains("ChangeTrackingCleanedUp")) {
    		ClientProvider clientProvider = (ClientProvider)syncAgent.LocalProvider;
    		foreach (SyncTable syncTable in syncAgent.Configuration.SyncTables) {
    			if (syncTable.SyncDirection != SyncDirection.Snapshot) {
    				clientProvider.SetTableReceivedAnchor(syncTable.TableName, new SyncAnchor());
    			}
    		}
    		syncStatistics = syncAgent.Synchronize();
    	} else {
    		throw;
    	}
    }

    Following this I expanded my support of this issue.
    I added an option to the config that could be set to: 

    • ClearTable (delete from client table, set sent and received anchor to null, ClientDeleteServerUpdate conflict)
    • ClearDatabase (delete from every table, set sent and received anchor to null, ClientDeleteServerUpdate conflict)
    • ResyncTable (set received anchor to null, ClientInsertServerInsert conflict) Works only if issue was server side
    • ResyncDatabase (set received anchor to null, ClientInsertServerInsert conflict) Works only if issue was server side
    • RecreateDatabase (drop, reconfigure and sync client database)
    • DoNothing

    When the ChangeTrackingCleanup issue is caught either client or server side then on the client side I would do different database actions depending on setting.
    If action fails then I would escalate to ResyncDatabase then RecreateDatabase.
    9 out of 10 times everything besides RecreateDatabase would fail. Everything besides RecreateDatabase fails if table that failed is the parent table for Foreign Key constraint.

    UPDATE:
    Escalating to RecreateDatabase does not always solve the issue, sometimes, although not consistently reproducible the RecreateDatabase will fail and I will only be able to solve by fully deleting both client and server databases.

    UPDATE 2:
    I have removed all the Change Tracking Cleaned Up issues by just removing the RAISEERROR from the Sql commands, seems to work fine, is there an issue with doing this?

    command.CommandText = command.CommandText.Replace(string.Format("IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'{0}')) > @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'{0}')", tablename), "");

    UPDATE 3:
    We abandoned Sql CT and just switched to using the newer Peer Providers (SqlSyncProvider) as this is much simpler, supports Bulk Changes and is the recommended approach.




    Monday, February 24, 2014 10:18 PM
  • Hi What is FaultException that you refering to? Regards
    Wednesday, April 09, 2014 4:12 PM
  • Hi What is FaultException that you refering to? Regards
    As this is an N-Tier solution and that code is Client side, all my Server service methods send exceptions back as FaultException. Therefore in this context a FaultException is an exception thrown from the Server service that implements the service interface.

    See update 3 to previous post
    Wednesday, May 07, 2014 3:33 PM