Umbraco - Delete Old Document Versions

Umbraco - Delete Old Document Versions

BEGIN TRANSACTION

/* Delete Old Document Versions */

Declare @keepOldVersionsCount int, @keepNewerThanDate datetime

Set @keepOldVersionsCount = 0 /* 0 Keeps published and newest only. */
Set @keepNewerThanDate = getdate() /* getDate() or '2013-01-01' */



IF OBJECT_ID('tempdb..#versions') IS NOT NULL DROP TABLE #versions

SELECT VersionID, nodeId, updateDate, newest, published  INTO #versions 
FROM cmsDocument
WHERE versionID NOT IN 
	(SELECT D.versionId 
	 FROM cmsDocument D 
	 WHERE D.versionId IN 
		(SELECT versionId 
		 FROM 
			(SELECT CV.versionId, published, newest, CV.versionDate, 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 <= (@keepOldVersionsCount + 2) OR tmp.published = 1 OR tmp.newest = 1 or tmp.versionDate >= @keepNewerThanDate)  
	 )

--DELETE FROM cmsContentVersion WHERE VersionId IN (select #versions.VersionId from #versions)
--DELETE FROM cmsPreviewXml WHERE versionId IN (select #versions.VersionId from #versions)
--DELETE FROM cmsPropertyData WHERE VersionId IN (select #versions.VersionId from #versions)
--DELETE FROM cmsDocument WHERE VersionId IN (select #versions.VersionId from #versions)

--select * from #versions


IF @@ERROR <> 0
	IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
	BEGIN
	   PRINT 'versions deleted'
	   COMMIT TRANSACTION
	END
ELSE
	BEGIN
	   PRINT 'error deleting versions, rollback transaciton.'
	END
GO