Last active
May 3, 2022 16:40
-
-
Save genckastrati/52413af6246078d6b9c35edf9a7ec4c0 to your computer and use it in GitHub Desktop.
Clean Database for Umbraco (7.14.x, 7.15.x)
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
-- Gets the current date and deducts 1 month | |
DECLARE @createdDate Datetime = DATEADD(m, -1, getdate()) | |
-- dump logs | |
DELETE FROM umbracolog WHERE Datestamp < @createdDate | |
-- clean up old versions where the updateDate is older then the date defined above | |
DELETE FROM cmsPropertyData WHERE | |
versionId NOT IN (SELECT versionId FROM cmsDocument WHERE published = 1 OR newest = 1) AND | |
contentNodeId IN (SELECT DISTINCT nodeID FROM cmsDocument) | |
DELETE FROM cmsPreviewXml WHERE | |
versionId NOT IN (SELECT versionId FROM cmsDocument WHERE published = 1 OR newest = 1) AND | |
nodeId IN (SELECT DISTINCT nodeID FROM cmsDocument) | |
DELETE FROM cmsContentVersion WHERE | |
versionId NOT IN (SELECT versionId FROM cmsDocument WHERE published = 1 OR newest = 1) AND | |
ContentId IN (SELECT DISTINCT nodeID FROM cmsDocument) | |
DELETE FROM cmsDocument WHERE | |
versionId NOT IN (SELECT versionId FROM cmsDocument WHERE published = 1 OR newest = 1) AND | |
nodeId IN (SELECT DISTINCT nodeID FROM cmsDocument) |
Disclaimer 3: Tried it out on Umbraco 7.15.3 on Umbraco Cloud and it seems to work fine.
Does not work on version 8. Will need to find an alternative.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Disclaimer 1: ALWAYS MAKE A BACKUP, before running scripts.
Disclaimer 2: Tried it out on Umbraco 7.14.0 on Umbraco Cloud and it seems to have worked. No issues detected. DB went from 350 MB to 19 MB.