Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Remove older content versions from the Umbraco database (tested on v7.9.2)
/*
This SQL will clean up older versions of content from the umbraco database. Note that that the "tmp.RowNum"-condition
is used to set the number of versions to keep for each content node this can be changed if needed.
Note 2: If you have lots of versions (like we had) you might need to execute the delete-statements with a offset/fetch-setup in steps.
*/
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
Select VersionID into #tmp FROM cmsDocument
WHERE versionID NOT IN (SELECT D.versionId FROM cmsDocument D
WHERE D.versionId IN (SELECT versionId FROM (SELECT CV.versionId, published, newest, RANK() OVER(ORDER BY CV.versionDate DESC) RowNum
FROM cmsContentVersion CV JOIN cmsDocument DD ON CV.versionId = DD.versionId
WHERE DD.nodeId = D.nodeId) AS tmp
WHERE tmp.RowNum <= 15 OR tmp.published = 1 OR tmp.newest = 1) )
SELECT * FROM #tmp
DELETE FROM cmsPreviewXml WHERE versionId IN (select #tmp.VersionId from #tmp)
DELETE FROM cmsContentVersion WHERE VersionId IN (select #tmp.VersionId from #tmp)
DELETE FROM cmsPropertyData WHERE VersionId IN (select #tmp.VersionId from #tmp)
DELETE FROM cmsDocument WHERE VersionId IN (select #tmp.VersionId from #tmp)
-- Use this for loops: order by #tmp.versionId OFFSET 0 ROWS FETCH NEXT 20000 ROWS ONLY
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment