Skip to content

Instantly share code, notes, and snippets.

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) )
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