K
kbachtold
Guest
We've run into this exact same situation. For the longest time RollBase had a bug where records that automatically purged from the recycle bin (after 30 days) would not cleanup related records in other tables. We have been cleaning up these tables specifically which have the most "orphaned records": - RB_RELATIONSHIP - RB_ACT_TRAIL Here is an example for RB_RELATIONSHIP that attempts to remove records not linked to anything else (SQL SERVER). It deletes batches of 5000 continuously until you stop the script. Use at your own risk! declare @count int declare @StrMsg nvarchar(100) set @count = 0 while 1=1 begin delete top (5000) r FROM RB.RB_RELATIONSHIP r LEFT OUTER JOIN RB.RB_OBJ_DATA od1 ON r.OBJ1_ID = od1.OBJ_ID LEFT OUTER JOIN RB.RB_USER_DATA ud1 ON r.OBJ1_ID = ud1.OBJ_ID LEFT OUTER JOIN RB.RB_DELETED_OBJS do1 ON r.OBJ1_ID = do1.OBJ_ID LEFT OUTER JOIN RB.RB_CUST_DATA c1 ON r.OBJ1_ID = c1.CUST_ID LEFT OUTER JOIN RB.RB_OBJ_DATA od2 ON r.OBJ2_ID = od2.OBJ_ID LEFT OUTER JOIN RB.RB_USER_DATA ud2 ON r.OBJ2_ID = ud2.OBJ_ID LEFT OUTER JOIN RB.RB_DELETED_OBJS do2 ON r.OBJ2_ID = do2.OBJ_ID LEFT OUTER JOIN RB.RB_CUST_DATA c2 ON r.OBJ2_ID = c2.CUST_ID LEFT OUTER JOIN RB.RB_CUST_DATA c on r.CUST_ID = c.CUST_ID WHERE ( (od1.OBJ_ID IS NULL AND ud1.OBJ_ID IS NULL AND do1.OBJ_ID IS NULL AND c1.CUST_ID IS NULL) OR (od2.OBJ_ID IS NULL AND ud2.OBJ_ID IS NULL AND do2.OBJ_ID IS NULL AND c2.CUST_ID IS NULL) ) AND r.OBJ1_ID 1000 AND r.OBJ2_ID 1000 AND r.OBJ_DEF1_ID 1000 AND r.OBJ_DEF2_ID 1000 set @count = @count + @@rowcount set @StrMsg = cast(@count as nvarchar(100)) RAISERROR(@StrMsg,0,1) WITH NOWAIT end -- Unfortunately we don't have a working script for RB_ACT_TRAIL but we are developing a similar one.
Continue reading...
Continue reading...