Umbraco V4.x Clearing old document revisions

This script will clear old document revisions ensuring the current revision isn’t removed.

Due to the way the Umbraco schema works if you have one doctype with 10 properties then publish that document 10 times you will have added 100 rows into the cmsPropertyData table and a number of rows into other tables through the CMS.

Please, please dont run this on a production database :) and remember it’s for the v4.x code base, I haven’t yet had chance to run it against v6.0.x

DECLARE @createdDate Datetime = '2012-03-01' --Change this!

DELETE FROM cmsPropertyData WHERE
versionId NOT IN (SELECT versionId FROM cmsDocument WHERE updateDate > @createdDate OR published = 1 OR newest = 1) AND
contentNodeId IN (SELECT DISTINCT nodeID FROM cmsDocument)

DELETE FROM cmsPreviewXml WHERE
versionId NOT IN (SELECT versionId FROM cmsDocument WHERE updateDate > @createdDate OR published = 1 OR newest = 1) AND
nodeId IN (SELECT DISTINCT nodeID FROM cmsDocument)

DELETE FROM cmsContentVersion WHERE
versionId NOT IN (SELECT versionId FROM cmsDocument WHERE updateDate > @createdDate OR published = 1 OR newest = 1) AND
ContentId IN (SELECT DISTINCT nodeID FROM cmsDocument)

DELETE FROM cmsDocument WHERE
versionId NOT IN (SELECT versionId FROM cmsDocument WHERE updateDate > @createdDate OR published = 1 OR newest = 1) AND
nodeId IN (SELECT DISTINCT nodeID FROM cmsDocument)