gftLaHSs

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