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

Umbraco - Delete Older Versions.sql

-- set how many versions to keep on line 21 (rn > n)
-- actually delete stuff by modifying line 34 to 'commit tran'
begin tran
go

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 > 20
	and
	published = 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