Cacher is the code snippet organizer for pro developers

-- ** Clean up old versions and other content from Umbraco Database
-- ** Compatible with SQLCE (eg run using LinqPad on an SDF file)

--https://gist.github.com/dampee/a8ead728165b16d49c00

-- Umbraco Clear Old Document Versions To Decrease Database Size And Improve Performance
-- http://borism.net/2008/12/16/fixing-a-large-cmspropertydata-table-in-umbraco/

DELETE FROM cmsPropertyData WHERE
    versionId NOT IN (SELECT versionId FROM cmsDocument WHERE updateDate > DATEADD(m, -1, getdate()) OR published = 1 OR newest = 1) AND
    contentNodeId IN (SELECT DISTINCT nodeID FROM cmsDocument)
	GO
	
DELETE FROM cmsPreviewXml WHERE
    versionId NOT IN (SELECT versionId FROM cmsDocument WHERE updateDate > DATEADD(m, -1, getdate()) OR published = 1 OR newest = 1) AND
    nodeId IN (SELECT DISTINCT nodeID FROM cmsDocument)
GO

DELETE FROM cmsContentVersion WHERE
    versionId NOT IN (SELECT versionId FROM cmsDocument WHERE updateDate > DATEADD(m, -1, getdate()) OR published = 1 OR newest = 1) AND
    ContentId  IN (SELECT DISTINCT nodeID FROM cmsDocument)
	GO
	
DELETE FROM cmsDocument WHERE
    versionId NOT IN (SELECT versionId FROM cmsDocument WHERE updateDate > DATEADD(m, -1, getdate()) OR published = 1 OR newest = 1) AND
    nodeId IN (SELECT DISTINCT nodeID FROM cmsDocument)
GO

--http://blog.dampee.be/post/2015/11/28/Remove-Old-versions-of-Umbraco-Previews.aspx
delete
--select *
from cmsPreviewXml
where versionId in (
    select cmsPreviewXml.versionId
    from cmsPreviewXml join cmsDocument on cmsPreviewXml.versionId=cmsDocument.versionId
    where cmsDocument.newest <> 1)
	GO
	
--https://gist.github.com/Hendy/1686288f7242cf4fdfab

-- Create a temporary table for all documents which are published and not in the recycle bin
CREATE TABLE Nodes (id int)
GO
-- Delete all rows if the table exists before
--TRUNCATE TABLE Nodes


-- Insert all nodeIds from all documents which are published and not in the recycle bin
INSERT INTO Nodes 
    SELECT N.id 
    FROM umbracoNode N
        INNER JOIN cmsDocument D ON N.ID = D.NodeId
    WHERE nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972'
        AND [path] NOT LIKE '%-20%'
        AND D.Published = 1
GO

-- Create a temporary table for all versionId's to delete
CREATE TABLE Versions (id UniqueIdentifier)
-- Delete all rows if it exists before
--TRUNCATE TABLE Versions
GO
-- Insert all versionId's from all nodeIds in the Nodes table 
-- and where published is set to false and newest is set to false
INSERT INTO Versions
    SELECT versionId 
    FROM cmsDocument 
    WHERE nodeId IN (SELECT id FROM Nodes) 
        AND published = 0 AND newest = 0
GO
-- DELETE all versions from cmsPropertyData, cmsContentVersion, cmsDocument
-- from the nodes which are published and which are not in the recycle bin 
-- and which are not published and which are not the newest

DELETE FROM cmsPreviewXml WHERE versionId IN (SELECT id FROM Versions)
GO

DELETE FROM cmsPropertyData WHERE VersionId IN (SELECT id FROM Versions)
GO

DELETE FROM cmsContentVersion WHERE VersionId IN (SELECT id FROM Versions)
GO

DELETE FROM cmsDocument WHERE VersionId IN (SELECT id FROM Versions)
GO

DROP TABLE Versions
GO

DROP TABLE Nodes

GO