Clean Database for Umbraco (7.14.x, 7.15.x)

clean-db-Umbraco.sql

-- Gets the current date and deducts 1 month
DECLARE @createdDate Datetime = DATEADD(m, -1, getdate())

-- dump logs
DELETE FROM umbracolog WHERE Datestamp < @createdDate

-- clean up old versions where the updateDate is older then the date defined above
DELETE FROM cmsPropertyData WHERE
    versionId NOT IN (SELECT versionId FROM cmsDocument WHERE 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 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 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 published = 1 OR newest = 1) AND
    nodeId IN (SELECT DISTINCT nodeID FROM cmsDocument)

Disclaimer 1: ALWAYS MAKE A BACKUP, before running scripts.

Disclaimer 2: Tried it out on Umbraco 7.14.0 on Umbraco Cloud and it seems to have worked. No issues detected. DB went from 350 MB to 19 MB.

Disclaimer 3: Tried it out on Umbraco 7.15.3 on Umbraco Cloud and it seems to work fine.

Does not work on version 8. Will need to find an alternative.