Umbraco 8 clean-up scripts

umbraco8cleanup.sql

PRINT '=============================================='
PRINT 'CLEARING LOG AND AUDIT TABLES'
PRINT '=============================================='
GO

PRINT '=============================================='
PRINT 'TRUNCATE TABLE umbracoLog'
PRINT '=============================================='
GO

TRUNCATE TABLE umbracoLog
GO

PRINT '=============================================='
PRINT 'TRUNCATE TABLE umbracoAudit'
PRINT '=============================================='
GO

TRUNCATE TABLE umbracoAudit
GO

PRINT '=============================================='
PRINT 'TRUNCATE TABLE umbracoUserLogin'
PRINT '=============================================='
GO
TRUNCATE TABLE umbracoUserLogin
GO

PRINT '=============================================='
PRINT 'Select all published document nodes'
PRINT '=============================================='
GO
-- Create a temporary table for all documents which are published and not in the recycle bin
DROP TABLE IF EXISTS #Nodes
CREATE TABLE #Nodes (id int)
GO

-- 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 umbracoDocument D ON N.ID = D.NodeId
    WHERE nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972'
        AND [path] NOT LIKE '%-20%'
        AND D.Published = 1
GO


PRINT '=============================================='
PRINT 'Select all unpublished document versions'
PRINT '=============================================='
GO
-- Create a temporary table for all versionId's to delete
DROP TABLE IF EXISTS #Versions
CREATE TABLE #Versions (id int)
GO

-- Insert all versionId's from all nodeIds in the #Nodes table 
-- and where published is set to false and [current] is set to false
INSERT INTO #Versions
    SELECT CV.id 
    FROM umbracoDocumentVersion AS DV INNER JOIN umbracoContentVersion AS CV ON DV.id = CV.id
    WHERE nodeId IN (SELECT id FROM #Nodes) AND published = 0 AND [current] = 0
GO

PRINT '=============================================='
PRINT 'DELETE unpublised versions from umbracoContentVersionCultureVariation, umbracoPropertyData, umbracoContentVersion, umbracoDocumentVersion '
PRINT '=============================================='
GO
-- DELETE all versions from umbracoContentVersionCultureVariation, umbracoPropertyData, umbracoContentVersion, umbracoDocumentVersion
-- 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

PRINT '=============================================='
PRINT 'DELETE FROM umbracoContentVersionCultureVariation'
PRINT '=============================================='
GO
DELETE FROM umbracoContentVersionCultureVariation WHERE versionId IN (SELECT id FROM #Versions)
GO


PRINT '=============================================='
PRINT 'DELETE FROM umbracoPropertyData'
PRINT '=============================================='
DELETE FROM umbracoPropertyData WHERE VersionId IN (SELECT id FROM #Versions)
GO

PRINT '=============================================='
PRINT 'DELETE FROM umbracoDocumentVersion'
PRINT '=============================================='
DELETE FROM umbracoDocumentVersion WHERE id IN (SELECT id FROM #Versions)
GO


PRINT '=============================================='
PRINT 'DELETE FROM umbracoContentVersion'
PRINT '=============================================='
GO
DELETE FROM umbracoContentVersion WHERE id IN (SELECT id FROM #Versions)
GO

-- Drop temp tables
PRINT '=============================================='
PRINT 'DROP #VERSIONS'
PRINT '=============================================='
GO
DROP TABLE #Versions
GO

PRINT '=============================================='
PRINT 'DROP #NODES'
PRINT '=============================================='
GO
DROP TABLE #Nodes
GO

PRINT '=============================================='
PRINT 'REINDEX TABLES'
PRINT '=============================================='
GO
-- Reindex tables  
DBCC DBREINDEX (umbracoPropertyData)
DBCC DBREINDEX (umbracoDocumentVersion)
DBCC DBREINDEX (umbracoContentVersion)
DBCC DBREINDEX (umbracoDocument)
DBCC DBREINDEX (umbracoContent)
DBCC DBREINDEX (umbracoContentVersionCultureVariation)
DBCC DBREINDEX (umbracoDomain)
DBCC DBREINDEX (umbracoAudit)
DBCC DBREINDEX (umbracoNode)
GO

PRINT '=============================================='
PRINT 'SHRINK DATABASE'
PRINT '=============================================='
GO
DBCC SHRINKDATABASE (0)
GO