How to fix a corrupt table

  • Updated

If you have a client who's database sync starts but never completes and you have exhausted most routes to resolve the issue, typically the last resort is to run the SQL Profiler on the database while the database sync is running.

This can provide valuable information on what is causing the database sync not to complete

Below is one issue that has come up before and the steps to fix this type of issue. This particular one comes from a user_to_group_tbl sync

Captured information from SQL Profiler:  filtered on database and textdata

declare @p5 int 
set @p5=0 
exec sync_user_to_group_tbl_bydelete @usergroup_id=1,@user_id=10737418260,@sync_min_timestamp=721023,@sync_force_write=1,@sync_row_count=@p5 output 
select @p5 

exec sync_user_to_group_tbl_selectrow @usergroup_id=1,@user_id=10737418260,@sync_scope_local_id=1 

If you look at the user_to_group_tbl_tracking at user_id=10737418260 and usergroup_id=1,@

Need to remove the 1 row having issue in user_to_group_tbl_tracking for Stage and Production

However if there are too many to fix you may need to clear the user_to_group_tbl_tracking and then you will need to rebuild the tracking table

select * from user_to_group_tbl_Tracking where user_id=10737418260

1             10737418260     NULL     NULL     NULL     NULL     0             0x0000000000646428     NULL               NULL     NULL     0             3995237              1             NULL     2014-01-28 11:58:09.217

 2             10737418260     NULL     NULL     NULL     NULL     0             0x0000000000646427     NULL               NULL     NULL     0             3995221              1             NULL     2014-01-28 11:58:09.217

select * from user_to_group_tbl_Tracking where user_id=10737418260 and usergroup_id=1

1             10737418260     NULL     NULL     NULL     NULL     0             0x0000000000646428     NULL               NULL     NULL     0             3995237              1             NULL     2014-01-28 11:58:09.217   

Clear the table:

Truncate table user_to_group_tbl_tracking

Rebuild tracking table:

Update user_to_group_tbl 
set user_id = user_id