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

Umbraco - Delete Older Versions.sql

/*  
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