0

We are running into performance issues with Azure Sql Data Sync that the tracking tables are massive, and can be larger than the source tables (2-10GB for tracking tables). This makes the datasync very slow and db intensive. This is especially painful in logging tables where we can generate gigs of data each month.

Reading https://jtabadero.wordpress.com/2012/08/23/things-you-need-to-know-about-sql-data-sync-service/ item 17

The Data Sync Service periodically does metadata clean-up. This removes entries from the tracking table that are more than the retention period.

For example, there is no point keeping the metadata for deleted rows when these changes has long been propagated to the member database.

Currently, this retention period is set to 45days. That means delete metadata for rows deleted more than 45 days ago are cleaned up. If a member has not synched within this retention period, the service will detect it as an outdated member and prevent that member from synching.

It sounds like there is a way to safely trim this data, is there a way to trigger this for a shorter period? Our database syncs numerous times throughout the day, and it would be safe to trim at 1 day of retention data.

Community
  • 1
  • 1
Aaron Sherman
  • 3,789
  • 1
  • 30
  • 33
  • Hi Aaron, I'm leaving this comment on behalf of the product team. They realized you have asked the same question on MSDN and they will use that thread to follow up with you on the issue. Thanks. – weidi Apr 01 '16 at 08:35
  • Thanks, I followed up with a very detailed description to them. When there is a solution I will post it here. – Aaron Sherman Apr 01 '16 at 15:21

1 Answers1

0
  1. Delete all the data you want to delete from the source database
  2. Run Replication and wait for it to finish
  3. I usually run it one more time, in case more data was deleted as part of normal operations
  4. Stop Replication from automatically running
  5. Delete tombstone=1 records from the tracking tables in both the hub and spoke database
  6. Turn your replication back on to automatic

I used the script below to more "gently" delete lots of data. by using the last sync time you can minimize chances you will remove the sync delete command.

declare @last_Sync datetime
set @last_Sync = '5-1-16 5:00pm'

select count(*) from datasync.XXX_dss_tracking with(nolock) where sync_row_is_tombstone=1 and last_change_datetime < @last_Sync

declare @i int
set @i=0

while (@i<100) begin
    delete from datasync.XXX_dss_tracking  where xxx_id  in 
        (select top 1000 xxx_id from datasync.XXX_dss_tracking with (nolock) where sync_row_is_tombstone=1 and last_change_datetime < @last_Sync)

    set @i=@i+1
end
Aaron Sherman
  • 3,789
  • 1
  • 30
  • 33