Created
December 2, 2015 01:19
-
-
Save barryokane/515e21542cb69e9e8dc1 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- ** 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 | |
No, I have not used this on V8.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
hello, has this been tested against Umbraco V8?
Thanks!