Remove older content versions from the Umbraco database (tested on v7.9.2)

Umbraco - Delete Older Versions.sql

-- set how many versions to keep using @numberOfVersionToKeep, this can be set to anything from 0 and above. 0 will clean all versions except the current of course.
-- actually delete stuff by modifying last line to 'commit tran'

begin tran
go

DECLARE @numberOfVersionToKeep int = 20

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP Table #tmp
create table #tmp (versionid uniqueidentifier)

insert #tmp select versionid from (
	select
		umbracoNode.id, umbracoNode.path,
		row_number() over ( partition by umbracoNode.id order by cmsDocument.updateDate desc) rn,
		cmsDocument.*
	from
		umbracoNode
		inner join cmsDocument on cmsDocument.nodeId = umbracoNode.id
	where
		nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972'
) t
where
	rn > @numberOfVersionToKeep
	and
	published = 0
	and
	newest = 0


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)

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP Table #tmp

go
rollback tran
--commit tran

Works also on Umbraco version 7.6.0 assembly: 1.0.6331.7966

Thanks for sharing!

Nice!! :) Thanks for letting me know :)

not work for v8

not work for v8

That's to be expected, the database model has changed a lot between V7 and V8.