Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Umbraco Database cleanup. After pulling in an umbraco database from production, you don't need all history or log.
-- dump logs
truncate table umbracolog
-- 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())
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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment