Cleaning up meta data in Azure SQL Data Sync
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.
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.
- Delete all the data you want to delete from the source database
- Run Replication and wait for it to finish
- I usually run it one more time, in case more data was deleted as part of normal operations
- Stop Replication from automatically running
- Delete tombstone=1 records from the tracking tables in both the hub and spoke database
- 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