· 7 years ago · Nov 20, 2018, 12:50 AM
1TRUNCATE TABLE umbracoLog
2GO
3TRUNCATE TABLE umbracoUser2NodePermission
4GO
5TRUNCATE TABLE umbracoUserLogins
6GO
7
8-- Create a temporary table for all documents which are published and not in the recycle bin
9CREATE TABLE #Nodes (id int)
10GO
11-- Delete all rows if the table exists before
12TRUNCATE TABLE #Nodes
13GO
14
15-- Insert all nodeIds from all documents which are published and not in the recycle bin
16INSERT INTO #Nodes
17 SELECT N.id
18 FROM umbracoNode N
19 INNER JOIN cmsDocument D ON N.ID = D.NodeId
20 WHERE nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972'
21 AND [path] NOT LIKE '%-20%'
22 AND D.Published = 1
23GO
24
25-- Create a temporary table for all versionId's to delete
26CREATE TABLE #Versions (id UniqueIdentifier)
27GO
28-- Delete all rows if it exists before
29TRUNCATE TABLE #Versions
30GO
31
32-- Insert all versionId's from all nodeIds in the #Nodes table
33-- and where published is set to false and newest is set to false
34INSERT INTO #Versions
35 SELECT versionId
36 FROM cmsDocument
37 WHERE nodeId IN (SELECT id FROM #Nodes) AND published = 0 AND newest = 0
38GO
39
40-- DELETE all versions from cmsPreviewXml, cmsPropertyData, cmsContentVersion, cmsDocument
41-- from the nodes which are published and which are not in the recycle bin
42-- and which are not published and which are not the newest
43DELETE FROM cmsPreviewXml WHERE versionId IN (SELECT id FROM #Versions)
44GO
45DELETE FROM cmsPropertyData WHERE VersionId IN (SELECT id FROM #Versions)
46GO
47DELETE FROM cmsContentVersion WHERE VersionId IN (SELECT id FROM #Versions)
48GO
49DELETE FROM cmsDocument WHERE VersionId IN (SELECT id FROM #Versions)
50GO
51
52-- Drop temp tables
53DROP TABLE #Versions
54GO
55DROP TABLE #Nodes
56GO
57
58-- Reindex tables
59DBCC DBREINDEX (cmsPropertyData)
60DBCC DBREINDEX (cmsPreviewXml)
61DBCC DBREINDEX (cmsContentVersion)
62DBCC DBREINDEX (cmsDocument)
63DBCC DBREINDEX (cmsContentXml)
64DBCC DBREINDEX (umbracoDomains)
65DBCC DBREINDEX (umbracoUser2NodePermission)
66DBCC DBREINDEX (umbracoNode)
67DBCC DBREINDEX (cmsContent)