none
Cannot enumerate changes at the DbServerSyncProvider exception that isn't resolved by deleting database. RRS feed

  • Question

  • I am writing a mobile client that utilises the sync framework. I have taken the synccomm example on codeplex to base my development. All seems to work well but I seem to have an issue with the following exception:

    Error getting changes.
     Cannot enumerate changes at the DbServerSyncProvider for table 'mobiess_Customer' in synchronization group 'Global'.
     SQL Server change tracking has cleaned up tracking information for table '[mobiess_Customer]'. To recover from this error, the client must reinitialize its local database and try to synchronize again.

    I understand that under certain circumstances this is caused by change tracking data being out of date, this I understand and have dealt with this issue in code. The problem I have is this occurs even on an empty database.

    I think I potentially understand why its happening but am unsure how I would go about fixing it since I believe the issue is within the framework and not my code.

    If I sync a table which has approximately 20 rows in it the sync client calls getchanges and synchronises the 20 rows as I can see them being synced in the trace file. What happens next is it appears to think there are more batches thus it calls getchanges again and at this point it throws the exception because it checks the new sync anchor which for the first sync is 150 (My batch size) which then throws the exception because the minimum batch anchor is 180456.

    I can supply any log files if that helps.

    Also is it possible to get the source code for the Sync Framework for the devices as debugging this kind of issue is really difficult when you have no source to step through.

    Russell

    Monday, February 15, 2010 11:26 PM

Answers

  • try something similar to this on your usp_GetNewBatchAnchor (sorry i dont have SQL with me now so this will be like pseudo SQL :) )
    this might help set the beginning anchor to a non empty batch:

    if sync_last_received_anchor is 0 or null
    set sync_last_received_anchor = CHANGE_TRACKING_MIN_VALID_VERSION
    create a new anchor storage (e.g. @temp_anchor)
    set @temp_anchor = @sync_last_received_anchor + batch_size
    declare @rows -- to hold row count
    while 1=1
     SELECT @rows=count(*)
     FROM dbo.mytable
      JOIN CHANGETABLE( CHANGES dbo.mytable, @sync_last_received_anchor ) CT ON CT.Id = dbo.mytable.Id
     WHERE  ( CT.SYS_CHANGE_CREATION_VERSION  <= @temp_anchor )
     
     if @rows > 0  -- check if this starting anchor returns rows, if it does, exit and use the current sync_last_received_anchor value
     begin
        break
     end
     else -- empty batch, so lets move on to the next batch
     begin
      set @sync_last_received_anchor = @temp_anchor
      set @temp_anchor = @sync_last_received_anchor + batch_size
     end

    end

    if it works, this would at least minimize the round trips between your client and server.

    Tuesday, February 16, 2010 1:57 AM
    Moderator

All replies

  • I think it is all to do with the usp_GetNewBatchAnchor stored procedure that was taken from the synccomm project. I think I need to change the line:

           -- If this is the first synchronization session for a database,
           -- get the lowest change version value from the tables. By
           -- default, Synchronization Services uses a value of 0 for
           -- sync_last_received_anchor on the first synchronization. If       
           -- you do not set @sync_last_received_anchor,
           -- this can cause empty batches to be downloaded until the
           -- lowest change version value is reached.
           IF @sync_last_received_anchor IS NULL OR
                  @sync_last_received_anchor = 0
           BEGIN
       SELECT @sync_last_received_anchor = 0 -- I think this needs setting.

    As the logs show hundreds of batches being requested which obviously takes some time.

    I shall continue my investigation.

    Monday, February 15, 2010 11:55 PM
  • have you tried setting the @sync_last_received_anchor to CHANGE_TRACKING_MIN_VALID_VERSION instead of 0?
    Tuesday, February 16, 2010 12:17 AM
    Moderator
  • have you tried setting the @sync_last_received_anchor to CHANGE_TRACKING_MIN_VALID_VERSION instead of 0?

    If I try that it reduces the batches a little but it still needs to process hunreds of empty batches. I have a snippet of my log file that shows all the calls to get changes:

    16-02 0027 INFO  - Session Progress: DownloadingChanges - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Session Progress: BatchCount,131 BatchNumber,41 MoreData,False  - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Table: mobiess_Customer has completed 0 of 0 changes. (0 failed) - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Invoking GetChanges - [3] - ServiceClient
    16-02 0027 INFO  - Session Progress: DownloadingChanges - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Session Progress: BatchCount,131 BatchNumber,42 MoreData,False  - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Table: mobiess_Customer has completed 0 of 0 changes. (0 failed) - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Invoking GetChanges - [3] - ServiceClient
    16-02 0027 INFO  - Session Progress: DownloadingChanges - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Session Progress: BatchCount,131 BatchNumber,43 MoreData,False  - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Table: mobiess_Customer has completed 0 of 0 changes. (0 failed) - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Invoking GetChanges - [3] - ServiceClient
    16-02 0027 INFO  - Session Progress: DownloadingChanges - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Session Progress: BatchCount,131 BatchNumber,44 MoreData,False  - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Table: mobiess_Customer has completed 0 of 0 changes. (0 failed) - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Invoking GetChanges - [3] - ServiceClient
    16-02 0027 INFO  - Session Progress: DownloadingChanges - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Session Progress: BatchCount,131 BatchNumber,45 MoreData,False  - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Table: mobiess_Customer has completed 0 of 0 changes. (0 failed) - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Invoking GetChanges - [3] - ServiceClient
    16-02 0027 INFO  - Session Progress: DownloadingChanges - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Session Progress: BatchCount,131 BatchNumber,46 MoreData,False  - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Table: mobiess_Customer has completed 0 of 0 changes. (0 failed) - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Invoking GetChanges - [3] - ServiceClient
    16-02 0027 INFO  - Session Progress: DownloadingChanges - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Session Progress: BatchCount,131 BatchNumber,47 MoreData,False  - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Table: mobiess_Customer has completed 0 of 0 changes. (0 failed) - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Invoking GetChanges - [3] - ServiceClient
    16-02 0027 INFO  - Session Progress: DownloadingChanges - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Session Progress: BatchCount,131 BatchNumber,48 MoreData,False  - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Table: mobiess_Customer has completed 0 of 0 changes. (0 failed) - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Invoking GetChanges - [3] - ServiceClient
    16-02 0027 INFO  - Session Progress: DownloadingChanges - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Session Progress: BatchCount,131 BatchNumber,49 MoreData,False  - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Table: mobiess_Customer has completed 0 of 0 changes. (0 failed) - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Invoking GetChanges - [3] - ServiceClient
    16-02 0027 INFO  - Session Progress: DownloadingChanges - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Session Progress: BatchCount,131 BatchNumber,50 MoreData,False  - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Table: mobiess_Customer has completed 0 of 0 changes. (0 failed) - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Invoking GetChanges - [3] - ServiceClient
    16-02 0027 INFO  - Session Progress: DownloadingChanges - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Session Progress: BatchCount,131 BatchNumber,51 MoreData,False  - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Table: mobiess_Customer has completed 0 of 0 changes. (0 failed) - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Invoking GetChanges - [3] - ServiceClient
    16-02 0027 INFO  - Session Progress: DownloadingChanges - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Session Progress: BatchCount,131 BatchNumber,52 MoreData,False  - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Table: mobiess_Customer has completed 0 of 0 changes. (0 failed) - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Invoking GetChanges - [3] - ServiceClient
    16-02 0027 INFO  - Session Progress: DownloadingChanges - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Session Progress: BatchCount,131 BatchNumber,53 MoreData,False  - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Table: mobiess_Customer has completed 0 of 0 changes. (0 failed) - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Invoking GetChanges - [3] - ServiceClient
    16-02 0027 INFO  - Session Progress: DownloadingChanges - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Session Progress: BatchCount,131 BatchNumber,54 MoreData,False  - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Table: mobiess_Customer has completed 0 of 0 changes. (0 failed) - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Invoking GetChanges - [3] - ServiceClient
    16-02 0027 INFO  - Session Progress: DownloadingChanges - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Session Progress: BatchCount,131 BatchNumber,55 MoreData,False  - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Table: mobiess_Customer has completed 0 of 0 changes. (0 failed) - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Invoking GetChanges - [3] - ServiceClient
    16-02 0027 INFO  - Session Progress: DownloadingChanges - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Session Progress: BatchCount,131 BatchNumber,56 MoreData,False  - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Table: mobiess_Customer has completed 0 of 0 changes. (0 failed) - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Invoking GetChanges - [3] - ServiceClient
    16-02 0027 INFO  - Session Progress: DownloadingChanges - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Session Progress: BatchCount,131 BatchNumber,57 MoreData,False  - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Table: mobiess_Customer has completed 0 of 0 changes. (0 failed) - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Invoking GetChanges - [3] - ServiceClient
    16-02 0027 INFO  - Session Progress: DownloadingChanges - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Session Progress: BatchCount,131 BatchNumber,58 MoreData,False  - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Table: mobiess_Customer has completed 0 of 0 changes. (0 failed) - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Invoking GetChanges - [3] - ServiceClient
    16-02 0027 INFO  - Session Progress: DownloadingChanges - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Session Progress: BatchCount,131 BatchNumber,59 MoreData,False  - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Table: mobiess_Customer has completed 0 of 0 changes. (0 failed) - [3] - Mobiess.Business.CF.Services.SyncEngineV2
    16-02 0027 INFO  - Invoking GetChanges - [3] - ServiceClient

    This is a bit slow on the console application I have but put it on a device and it takes forever.
    Tuesday, February 16, 2010 12:29 AM
  • try something similar to this on your usp_GetNewBatchAnchor (sorry i dont have SQL with me now so this will be like pseudo SQL :) )
    this might help set the beginning anchor to a non empty batch:

    if sync_last_received_anchor is 0 or null
    set sync_last_received_anchor = CHANGE_TRACKING_MIN_VALID_VERSION
    create a new anchor storage (e.g. @temp_anchor)
    set @temp_anchor = @sync_last_received_anchor + batch_size
    declare @rows -- to hold row count
    while 1=1
     SELECT @rows=count(*)
     FROM dbo.mytable
      JOIN CHANGETABLE( CHANGES dbo.mytable, @sync_last_received_anchor ) CT ON CT.Id = dbo.mytable.Id
     WHERE  ( CT.SYS_CHANGE_CREATION_VERSION  <= @temp_anchor )
     
     if @rows > 0  -- check if this starting anchor returns rows, if it does, exit and use the current sync_last_received_anchor value
     begin
        break
     end
     else -- empty batch, so lets move on to the next batch
     begin
      set @sync_last_received_anchor = @temp_anchor
      set @temp_anchor = @sync_last_received_anchor + batch_size
     end

    end

    if it works, this would at least minimize the round trips between your client and server.

    Tuesday, February 16, 2010 1:57 AM
    Moderator
  • I shall give that a go and will let you know if i have success.

    Thanks.
    Tuesday, February 16, 2010 10:14 AM
  • I had some success with you suggestion but it appears that by implementing this it would always get one batch. I assume because sync services wants to know how many batches it has and doesn't call the stored procedure to get the next batch sequence.

    I have decided to switch off batching until I have a little more control over what gets sent back down the wire.

    Regards
    Russell
    Thursday, February 25, 2010 7:58 PM
  • you're right, it would always get at least one batch. The get new batch anchor stored procedure simply set's the low and high watermarks for batching. the values are then passed on to GetChanges to retrieve the rows.
    Friday, February 26, 2010 1:02 AM
    Moderator
  • I have implemented a hack for batching which I have attached below. This does the batching on the server by paging the results and telling the client there are more pages. It isn't elegant but it works, I am going to spend more time tidying it up but for the moment it overcomes the limitation of empty batches.

    By keeping track of the anchors when a request comes from a client for the next batch I reset the anchors and retrieve the data so it returns the same set as the last request, I then remove all the rows that are not part of the batch and send back the result while also incrementing the batch number.

    This assumes that only one table is sync at a time.

    You must also ensure you send up a @Session parameter that is a random guid that uniquely identifies this client request.

     

    private static Dictionary<string, Batch> sessionProgress = new Dictionary<string, Batch>();

     

     

    public SyncContext GetChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession)

            {

                var pageSize = 1000;

                var syncProvider = CreateChangeTrackingProvider(syncSession);

                SyncContext context;

                try

                {

                    var key = syncSession.SyncParameters["@Session"].Value + "-" + groupMetadata.GroupName;

                    if (sessionProgress.ContainsKey(key) && sessionProgress[key].Current <= sessionProgress[key].Total)

                    {

                        groupMetadata.TablesMetadata[0].LastSentAnchor = sessionProgress[key].LastSentAnchor;

                        groupMetadata.TablesMetadata[0].LastReceivedAnchor = sessionProgress[key].LastReceivedAnchor;

                    }

                    else

                        sessionProgress.Remove(key);

     

                    context = syncProvider.GetChanges(groupMetadata, syncSession);

                    if (context.DataSet.Tables.Count == 1)

                    {

                        var dataTable = context.DataSet.Tables[0];

                        if (dataTable.Rows.Count <= pageSize)

                            return context;

     

                        var currentBatch = 0;

                        if(groupMetadata.BatchCount == 0)

                        {

                            var batches = Convert.ToInt32(Math.Ceiling(Convert.ToDecimal(dataTable.Rows.Count) / Convert.ToDecimal(pageSize))) - 1;

                            if (sessionProgress.ContainsKey(key))

                                sessionProgress.Remove(key);

     

                            sessionProgress.Add(key, new Batch()

                                                         {

                                                             Current = 0, 

                                                             Total = batches,

                                                             LastReceivedAnchor = groupMetadata.TablesMetadata[0].LastReceivedAnchor,

                                                             LastSentAnchor = groupMetadata.TablesMetadata[0].LastSentAnchor

                                                         });

                            currentBatch = batches;

                        }

                        else

                        {

                            sessionProgress[key].Current += 1;

                            currentBatch = sessionProgress[key].Total - sessionProgress[key].Current;

                        }

     

                        groupMetadata.BatchCount = sessionProgress[key].Current;

                        context.BatchCount = sessionProgress[key].Total + 1;

     

                        var startRange = currentBatch * pageSize;

                        var endRange = startRange + pageSize - 1;

                        for(var i = dataTable.Rows.Count - 1; i >=0 ; i--)

                        {

                            if(i < startRange || i > endRange)

                                dataTable.Rows.RemoveAt(i);

                        }

                    }

                }

                catch (Exception e)

                {

                    Guid id = Guid.NewGuid();

                    throw new FaultException<string>(e.Message, new FaultReason(

                                    new FaultReasonText(GetExceptionMessage("Error getting changes.",e))),

                        FaultCode.CreateSenderFaultCode(id.ToString(), "urn:synccomm.com/2009/07/ISyncService"));

     

                } 

                return context;

     }

     

     

     

     public class Batch

     {

         public int Current { get; set; }

         public int Total { get; set; }

         public SyncAnchor LastSentAnchor { get; set; }

         public SyncAnchor LastReceivedAnchor { get; set; }

     }

     

     


    Tuesday, April 19, 2011 10:42 PM