Umbraco Version Cleanup - Ultrascript

 Just thought I'd share my Umbraco cleanup script.

If you're looking to remove old versions, clean up your recycle bin, and generally speed up your database, this is the script for you. It is a combination of all the scripts I have found out in the wild.

Warning: Back up your DB before you run this. You have been warned!

truncate table umbracoLog 

-- truncate elmah table if it exist
IF OBJECT_ID('ELMAH_Error') IS NOT NULL
TRUNCATE TABLE ELMAH_Error

-- empty content recycle bin
DELETE FROM cmsPreviewXml WHERE nodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20)
DELETE FROM cmsContentVersion WHERE contentId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20)
DELETE FROM cmsDocument WHERE nodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20)
DELETE FROM cmsContentXML WHERE nodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20)
DELETE FROM cmsContent WHERE nodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20)
DELETE FROM cmsPropertyData WHERE contentNodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20)

-- delete the XML nodes...
DELETE FROM umbracoDomains WHERE domainRootStructureID IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20)
DELETE FROM umbracoUser2NodePermission WHERE nodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20)
DELETE FROM umbracoRelation WHERE parentId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20)
DELETE FROM umbracoRelation WHERE childId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20)
DELETE FROM cmsTask WHERE nodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20)
DELETE FROM cmsTagRelationship WHERE nodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20)
DELETE FROM dbo.umbracoRedirectUrl WHERE contentKey IN (SELECT UniqueId FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20)
DELETE FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20

-- empty media recycle bin 
delete from dbo.cmsContentVersion where contentid in (select Id from dbo.umbracoNode where path like '%-21%' and Id !=-21)
delete from dbo.cmsContentXml where nodeId in (select Id from dbo.umbracoNode where path like '%-21%' and Id !=-21)
delete from dbo.cmsMedia where nodeId in (select Id from dbo.umbracoNode where path like '%-21%' and Id !=-21)
delete from dbo.cmsContent where nodeId in (select Id from dbo.umbracoNode where path like '%-21%' and Id !=-21)
delete from dbo.cmsPropertyData where contentNodeId in (select Id from dbo.umbracoNode where path like '%-21%' and Id !=-21)

-- delete property data versions
DECLARE @createdDate Datetime = getDate()
 
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)
  

-- reindex tables  
DBCC DBREINDEX (cmsPropertyData)
DBCC DBREINDEX (cmsPreviewXml)
DBCC DBREINDEX (cmsContentVersion)
DBCC DBREINDEX (cmsDocument)
DBCC DBREINDEX (cmsContentXml)
DBCC DBREINDEX (umbracoDomains)
DBCC DBREINDEX (umbracoUser2NodePermission)
DBCC DBREINDEX (umbracoNode)
DBCC DBREINDEX (cmsContent)

    
-- shrink database files
DECLARE @logFile nvarchar(200) = (select Name from sys.database_files where Name like '%log')
select @logFile
DBCC SHRINKFILE(@logFile)

DECLARE @dataFile nvarchar(200) = (select Name from sys.database_files where Name like 'data%')
select @dataFile
DBCC SHRINKFILE(@dataFile)