none
PerformCleanUp clean all the metadata even the RetentionInDays = 30 RRS feed

  • Question

  • Hello all,

    My code is like this:

                    SqlSyncStoreMetadataCleanup metadataCleanup = new SqlSyncStoreMetadataCleanup(new SqlConnection(item.ConnectionString),
                        null,
                        schemaPrefix,
                        intentionDays); //intentionDays = 30
                    metadataCleanup.CommandTimeout = 1800;
                    metadataCleanup.PerformCleanup();

    But all the metadata was be clean with sync_row_is_tombstone = 1, including the records with the last_change_time < 30d,

    Thanks a lot


    Get reply from social.microsoft

    Monday, July 30, 2012 2:44 AM

All replies

  • can you confirm the SQL statement being run by Sync Fx using SQL Profiler?
    Monday, July 30, 2012 1:49 PM
    Moderator
  • June,

    Thanks a lot for your reply.

    There are more than 300,000 records in the RBOTransactionPaymentTrans_tracking table with Sync_row_is_tombstone = 1

    And the last_change_datetime of  the most records is 2012-06-12, the local_update_peer_timeStamp of the last one record at 2012-06-12 is 33604752

    the last_change_datetime of the some records is 2012-07-30, the local_update_timestamp of the last one record at 2012-07-30 is 36599963

    But when I execute the PerformCleanup with IntentionDays = 30, all the records are cleanup.

    The SQL statement I got by SQL Profiler:

    declare @p4 bigint
    set @p4=NULL
    exec sp_executesql N'SELECT @maxTS = MAX([local_update_peer_timestamp]) FROM [POSSync].[RBOTRANSACTIONPAYMENTTRANS_tracking] WHERE DATEDIFF(dd, [last_change_datetime], GETDATE()) >= @retInDays AND [local_update_peer_timestamp] <= @upperBoundTS AND [sync_row_is_tombstone] = 1;',N'@retInDays int,@maxTS bigint output,@upperBoundTS bigint',@retInDays=30,@maxTS=@p4 output,@upperBoundTS=38500546
    select @p4
    go

    declare @p4 bigint
    set @p4=37107241
    exec sp_executesql N'SELECT @maxTS = MAX([local_update_peer_timestamp]) FROM [POSSync].[RBOTRANSACTIONPAYMENTTRANS_tracking] WHERE DATEDIFF(dd, [last_change_datetime], GETDATE()) >= @retInDays AND [local_update_peer_timestamp] <= @upperBoundTS AND [sync_row_is_tombstone] = 1;',N'@retInDays int,@maxTS bigint output,@upperBoundTS bigint',@retInDays=30,@maxTS=@p4 output,@upperBoundTS=38500546
    select @p4
    go

    exec sp_executesql N'DECLARE @rows int;BEGIN TRANSACTION
    DELETE top(100000) FROM [POSSync].[RBOTRANSACTIONPAYMENTTRANS_tracking] WHERE [local_update_peer_timestamp] <= @upperBoundTS AND [sync_row_is_tombstone] = 1
    SET @rows = @@ROWCOUNT;
    COMMIT TRANSACTION
    SELECT @rows',N'@retInDays int,@upperBoundTS bigint',@retInDays=30,@upperBoundTS=38164228
    go

    exec sp_executesql N'DECLARE @rows int;BEGIN TRANSACTION
    DELETE top(100000) FROM [POSSync].[RBOTRANSACTIONPAYMENTTRANS_tracking] WHERE [local_update_peer_timestamp] <= @upperBoundTS AND [sync_row_is_tombstone] = 1
    SET @rows = @@ROWCOUNT;
    COMMIT TRANSACTION
    SELECT @rows',N'@retInDays int,@upperBoundTS bigint',@retInDays=30,@upperBoundTS=38164228
    go

    exec sp_executesql N'DECLARE @rows int;BEGIN TRANSACTION
    DELETE top(100000) FROM [POSSync].[RBOTRANSACTIONPAYMENTTRANS_tracking] WHERE [local_update_peer_timestamp] <= @upperBoundTS AND [sync_row_is_tombstone] = 1
    SET @rows = @@ROWCOUNT;
    COMMIT TRANSACTION
    SELECT @rows',N'@retInDays int,@upperBoundTS bigint',@retInDays=30,@upperBoundTS=38164228
    go

    exec sp_executesql N'DECLARE @rows int;BEGIN TRANSACTION
    DELETE top(100000) FROM [POSSync].[RBOTRANSACTIONPAYMENTTRANS_tracking] WHERE [local_update_peer_timestamp] <= @upperBoundTS AND [sync_row_is_tombstone] = 1
    SET @rows = @@ROWCOUNT;
    COMMIT TRANSACTION
    SELECT @rows',N'@retInDays int,@upperBoundTS bigint',@retInDays=30,@upperBoundTS=38164228
    go

    exec sp_executesql N'DECLARE @rows int;BEGIN TRANSACTION
    DELETE top(100000) FROM [POSSync].[RBOTRANSACTIONPAYMENTTRANS_tracking] WHERE [local_update_peer_timestamp] <= @upperBoundTS AND [sync_row_is_tombstone] = 1
    SET @rows = @@ROWCOUNT;
    COMMIT TRANSACTION
    SELECT @rows',N'@retInDays int,@upperBoundTS bigint',@retInDays=30,@upperBoundTS=38164228
    go

    exec sp_executesql N'DECLARE @rows int;BEGIN TRANSACTION
    DELETE top(100000) FROM [POSSync].[RBOTRANSACTIONPAYMENTTRANS_tracking] WHERE [local_update_peer_timestamp] <= @upperBoundTS AND [sync_row_is_tombstone] = 1
    SET @rows = @@ROWCOUNT;
    COMMIT TRANSACTION
    SELECT @rows',N'@retInDays int,@upperBoundTS bigint',@retInDays=30,@upperBoundTS=38164228
    go

    exec sp_executesql N'DECLARE @rows int;BEGIN TRANSACTION
    DELETE top(100000) FROM [POSSync].[RBOTRANSACTIONPAYMENTTRANS_tracking] WHERE [local_update_peer_timestamp] <= @upperBoundTS AND [sync_row_is_tombstone] = 1
    SET @rows = @@ROWCOUNT;
    COMMIT TRANSACTION
    SELECT @rows',N'@retInDays int,@upperBoundTS bigint',@retInDays=30,@upperBoundTS=38164228
    go

    exec sp_executesql N'DECLARE @rows int;BEGIN TRANSACTION
    DELETE top(100000) FROM [POSSync].[RBOTRANSACTIONPAYMENTTRANS_tracking] WHERE [local_update_peer_timestamp] <= @upperBoundTS AND [sync_row_is_tombstone] = 1
    SET @rows = @@ROWCOUNT;
    COMMIT TRANSACTION
    SELECT @rows',N'@retInDays int,@upperBoundTS bigint',@retInDays=30,@upperBoundTS=38164228
    go

    exec sp_executesql N'DECLARE @rows int;BEGIN TRANSACTION
    DELETE top(100000) FROM [POSSync].[RBOTRANSACTIONPAYMENTTRANS_tracking] WHERE [local_update_peer_timestamp] <= @upperBoundTS AND [sync_row_is_tombstone] = 1
    SET @rows = @@ROWCOUNT;
    COMMIT TRANSACTION
    SELECT @rows',N'@retInDays int,@upperBoundTS bigint',@retInDays=30,@upperBoundTS=38164228
    go

    exec sp_executesql N'DECLARE @rows int;BEGIN TRANSACTION
    DELETE top(100000) FROM [POSSync].[RBOTRANSACTIONPAYMENTTRANS_tracking] WHERE [local_update_peer_timestamp] <= @upperBoundTS AND [sync_row_is_tombstone] = 1
    SET @rows = @@ROWCOUNT;
    COMMIT TRANSACTION
    SELECT @rows',N'@retInDays int,@upperBoundTS bigint',@retInDays=30,@upperBoundTS=38164228
    go


    Get reply from social.microsoft

    Wednesday, August 1, 2012 4:11 AM
  • I decompile the code,

    I found that the @upperBoundTS for the delete statement is the max value between 
    ForgottenKnowledge.FindMinTickCountForReplica(handler.ScopeSyncId)) and the value from 

    SELECT @maxTS = MAX([local_update_peer_timestamp]) FROM [POSSync].[RBOTRANSACTIONPAYMENTTRANS_tracking] WHERE DATEDIFF(dd, [last_change_datetime], GETDATE()) >= @retInDays AND [local_update_peer_timestamp] <= @upperBoundTS AND [sync_row_is_tombstone] = 1

    So I am worry about there is something wrong with the ForgottenKnowledge

    The database is restored from a site, and I execute "PerformPostRestoreFixup". why it still not work?

    But when I de_provision the database and provision it, "PerformCleanup" works well.

    what is wrong with "PerformPostRestoreFixup", it should update the Tickcount for the ForgottenKnowledge.

    Thanks,

    Abby


    Get reply from social.microsoft

    Friday, August 10, 2012 3:12 AM
  • have you done a comparison of the upperBoundTS  when doing a performcleanup before and after a post restore fixup?
    Friday, August 10, 2012 7:56 AM
    Moderator
  • I didn't do it.

    You think the post restore fixup does not work??

    But even we know the post restore fixup not work, what can I do?

    Any way, I will do some test on it.

    Thanks a lot,

    Abby


    Get reply from social.microsoft

    Friday, August 10, 2012 8:09 AM
  • i just dont have the exact environment to replicate your issue :)

    right now, post restore is looking to be the culprit, so a quick test on your side might help isolate the issue instead of groping on the cleanup and restore codes...

    Friday, August 10, 2012 9:02 AM
    Moderator