In a migration project where I had to migrate a lot of pages, about 400k pages with multiple blocks each, where we had an incremental approach with Migration of batches, testing, fixing and remigration i really filled up my database with a lot of “deleted” items which I was not aware of- and that for a long time 🙂
At the very end I was wondering why “deletion” of pages via the “Empty Trash” button and the “Automatic emptying of trash” job started not to work out as expected, but I think just in the case if you have tons of items in the trash which are also a child of single container pages can have an impact.
So I tried to find a way to not use the Optimizely backend UI and found out that there is a way to query the database how many content items are marked as deleted
select * from tblContent where Deleted = 1
In my case I had over 150.000 deleted content items where the most even were not displayed in the trash.
Then for the deletion of these there is a built in stored procedure which you can execute
EXEC editDeleteChilds @PageID = 2, @ForceDelete = 1
Also with the executing of this one I had the impression it is again doing nothing while execution.
But because of my huge amount of for deletion marked items I was not really aware of that a single execution of this stored procedure editDeleteChilds really removes 5000 items per run. Packing that into a loop helped me to get rid of them very fast.
So if you also have a huge amount of items in the trash and cleaning up with the Opimizely UI does not seem to help you quick, you can query and execute this stored procedure to get a clean trash again.