Last active
February 7, 2023 23:08
-
-
Save genckastrati/0e7f7ae6156647e00a56eb5ef403166d to your computer and use it in GitHub Desktop.
Clean Database for Umbraco 7.5.14 - This SQL script will clean up the database removing old versions for pages, audit and rollback information which can cause timeouts and delays for Courier
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
TRUNCATE TABLE umbracoLog | |
GO | |
TRUNCATE TABLE umbracoServer | |
GO | |
TRUNCATE TABLE umbracoUser2NodePermission | |
GO | |
TRUNCATE TABLE umbracoCacheInstruction | |
GO | |
TRUNCATE TABLE umbracoRedirectUrl | |
GO | |
-- 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 | |
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 cmsDocument D ON N.ID = D.NodeId | |
WHERE [path] NOT LIKE '%-20%' | |
AND D.Published = 1 | |
GO | |
-- Create a temporary table for all versionId's to delete | |
CREATE TABLE #Versions (id UniqueIdentifier) | |
GO | |
-- 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 cmsPreviewXml, 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 temp tables | |
DROP TABLE #Versions | |
GO | |
DROP TABLE #Nodes | |
GO | |
-- Reindex tables | |
DBCC DBREINDEX (cmsPropertyData) | |
GO | |
DBCC DBREINDEX (cmsPreviewXml) | |
GO | |
DBCC DBREINDEX (cmsContentVersion) | |
GO | |
DBCC DBREINDEX (cmsDocument) | |
GO | |
DBCC DBREINDEX (cmsContentXml) | |
GO | |
DBCC DBREINDEX (umbracoDomains) | |
GO | |
DBCC DBREINDEX (umbracoUser2NodePermission) | |
GO | |
DBCC DBREINDEX (umbracoNode) | |
GO | |
DBCC DBREINDEX (cmsContent) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment