The database of my SilverStripe website gets too big. When I have a look at the mysql tables it turns out SiteTree_versions and PageView are the culprits.

I could remove all content for PageViews, but I'd suggest a nice button in the admin interface saying: "clean up database from old visits ~ +3 months ago", for instance.

I could as well drop SiteTree_versions content manually but this might prevent me from reverting to the current revision with further editing, as pointed out here: http://www.silverstripe.org/archive/show/166007

Could you please give me the mysql code to get rid of all history versions BUT THE MOST RECENT, for every page of the current site tree? Would be awesome... but I just can't figure the mysql command... and besides: linking it to a button in admin interface would be so coool ;o)

Thanks for any input,
Berteh.

I imagine you'd be best of learning how to do this using the object-relational layer SS provides rather than a direct MySQL queries. You'll get to understand better what SS is doing with all these version files, I reckon. Have a look at the "Versioned" class in saphire/core/model.

You could try this script, but be _careful_ and make a database backup before:

DELETE FROM sitetree_versions
WHERE CONCAT_WS('_', RecordID, Version) NOT IN
(SELECT CONCAT_WS('_', id, Version) AS combined_id FROM sitetree);

thanks weberho, your sql code made it after quick case correction.

To clean pageview I executed a stupid
DELETE FROM `PageView` WHERE ID < 225745
after having spotted a record with a date that would match the time slot I wanted to keep.

These 2 queries allowed me to save ~25Mb out of a 55Mb database.

Tobych: I might have a look into it someday, thanks for the pointer.

has anyone got a fix for this? I am keen to remove pages that are more than six month old or something like that.

Also see:
http://groups.google.com/group/silverstripe-dev/browse_thread/thread/6b66facdc7ccc3f0

http://www.silverstripe.org/customising-the-cms/show/5190

https://github.com/silverstripe-labs/silverstripe-recipes/blob/master/model/tools/SanitiseTasks.php
(this last one seems to do the trick)

This is what I used - hope someone else finds this useful.

https://github.com/axllent/silverstripe-version-truncator