Is anyone aware of any C# or SQL scripts to perform a database cleanup? Looking to compact the data set of things like old log entries, unused data types, unused doc types, old/unused content versions, etc.
Really nice script from Damiaan - remove log entries and clean up old versions and previews:
-- Umbraco Clear Old Document Versions To Decrease Database Size And Improve Performance
-- http://borism.net/2008/12/16/fixing-a-large-cmspropertydata-table-in-umbraco/
DECLARE @createdDate Datetime = DATEADD(m, -1, getdate())
-- dump logs
-- TRUNCATE TABLE umbracolog -- faster if log table is very big and you don't need anything
DELETE FROM umbracolog WHERE Datestamp < @createdDate
-- clean up old versions
DELETE FROM cmsPropertyData WHERE
versionId NOT IN (SELECT versionId FROM cmsDocument WHERE updateDate > @createdDate OR published = 1 OR newest = 1) AND
contentNodeId IN (SELECT DISTINCT nodeID FROM cmsDocument)
DELETE FROM cmsPreviewXml WHERE
versionId NOT IN (SELECT versionId FROM cmsDocument WHERE updateDate > @createdDate OR published = 1 OR newest = 1) AND
nodeId IN (SELECT DISTINCT nodeID FROM cmsDocument)
DELETE FROM cmsContentVersion WHERE
versionId NOT IN (SELECT versionId FROM cmsDocument WHERE updateDate > @createdDate OR published = 1 OR newest = 1) AND
ContentId IN (SELECT DISTINCT nodeID FROM cmsDocument)
DELETE FROM cmsDocument WHERE
versionId NOT IN (SELECT versionId FROM cmsDocument WHERE updateDate > @createdDate OR published = 1 OR newest = 1) AND
nodeId IN (SELECT DISTINCT nodeID FROM cmsDocument)
--http://blog.dampee.be/post/2015/11/28/Remove-Old-versions-of-Umbraco-Previews.aspx
delete
--select *
from cmsPreviewXml
where versionId in (
select cmsPreviewXml.versionId
from cmsPreviewXml join cmsDocument on cmsPreviewXml.versionId=cmsDocument.versionId
where cmsDocument.newest <> 1)
Database Cleanup
Is anyone aware of any C# or SQL scripts to perform a database cleanup? Looking to compact the data set of things like old log entries, unused data types, unused doc types, old/unused content versions, etc.
Hello Michael
Have a look at this post - https://our.umbraco.com/forum/umbraco-cloud/98324-clean-up-recycle-bin-with-75000-nodes
Similar Question.
Thanks, Alex
Thank Alex, It's very helpful!
Really nice script from Damiaan - remove log entries and clean up old versions and previews:
Thanks,
Alex
Thanks Alex, but I need one for Umbraco 8 (that's the category of my post actually).
Any chance you found a way to do this yet?
I also look for a way to do this automatically on Umbraco 8.
Anything new here ? :)
is working on a reply...