Recycle Bin

Regarding the cleanup performance issue :

We have had a similar request some time back (community.progress.com/.../49682.aspx) and we are working on it.

Hi Fil,

Some improvements like viewing records via object type is already in place and available in the live product. Check out this thread for more info community.progress.com/.../47407.aspx

We will bring in more enhancements to the RecycleBin in the coming releases. Request you to post under this idea particular features that you would like to see.. community.progress.com/.../creating_views_for_recycle_bin.aspx

I think the features as describe in those posts are appropriate - some sort of filtering similar to a view so you can search the recycle bin and selectively restore/purge records. This would also need to extend to being able to select more than 20 records at a time like you can on normal object views as well.

The other major feature is major improvements on purging efficiencies. When I try and empty the recycle bin with more than around 200,000 records, the system has a major heart attack, sometimes crashes, with memory errors, and purges nothing. Purging the recycle bin 20 at a time for that number of total records is not fun or practical.

In this instance, I had 345,000 records. To answer my first question in my opening post, I've gone directly into the database engine (MySQL) deleted them from the RB_DELETED_OBJS table and it seems to have coped, although I think I should've stopped Rollbase first. Is there anything else we should do after having emptied the table from the database?

F

As part of the purge rollbase tries to cleanup not only the selected records from RB_DELETED_OBJS but also there dependent records from other tables such as relationship etc. Now that you have manually deleted it from RB_DELETED_OBJS, the entries in other tables would not have been removed.

Fil,

Deleting from RB_DELETED_OBJS only will most probably have side effects on the system (e.g. ghost records, relationships, etc). We understand that bulk delete from recycle bin to the tune of 100k+ records is not a pleasant activity today, and rest assure that we are working on it. However, until such a feature is available, we do NOT recommend emptying RB_DELETED_OBJS only via SQL.

I suspected something like that, but I was left with no choice. The recycle bin refused to empty in any fashion apart from 20 at a time. Will data maintenance do anything about that or should I attempt to manually cleanup RB_RELATIONSHIP. Other tables?

F

Thanks guys. If there are any pointers as to which tables I can manually cleanup, happy to do so.

Deletion of a large number of records sucks. I have found a way around for now and happy to post my first hack for Rollbase... :)

You will need to put the following script into your application's custom sidebar (Application setup > your application > more actions > Custom Sidebar
Caveats:
- Will result in deleting all records in Recyclebin (so be careful)
- To abort at any stage you will have to move away from the Recyclebin page (closing the browser tab is most effective)
- Because the next page renders after the current 20 records have been deleted, I'd assume the previous 20 were successfully deleted.

$(document).ready(function() {
	if (window.location.href.indexOf('recycleBin.jsp') > -1) {
		// Cleanup recyclebin
		rbf_checkListBoxes(document.recBin, true, 'sel_', 'recBin');
		var buff = rbf_selectedBoxes(document.recBin, 'sel_');
		if (buff.length == 0) {
			return;
		}
		window.location.href = 'recycleBin.jsp?act=purgeGroup&ids='+buff;
	}
});

And when your work is done, you would want to remove the script from the custom sidebar. Or else, any items in the Recycle bin would be deleted if anyone visits it. :)

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.