Press Ctrl / CMD + C to copy this to your clipboard.
This post will be reported to the moderators as potential spam to be looked at
I've got size issues with my database and I was wanting to delete all version history and audit trail.
Is there any way to remove the history without installing a package? Maybe an SQL statement?
The reason I ask is I can't install any packages just now due to the database size being too big on my hosting.
Here is a SQL script that I use to clean an Umbraco database:
TRUNCATE TABLE umbracoLog
TRUNCATE TABLE umbracoUser2NodePermission
TRUNCATE TABLE umbracoUserLogins
-- Create a temporary table for all documents which are published and not in the recycle bin
CREATE TABLE #Nodes (id int)
-- 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
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
-- 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
-- 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
WHERE nodeId IN (SELECT id FROM #Nodes) AND published = 0 AND newest = 0
-- 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)
DELETE FROM cmsPropertyData WHERE VersionId IN (SELECT id FROM #Versions)
DELETE FROM cmsContentVersion WHERE VersionId IN (SELECT id FROM #Versions)
DELETE FROM cmsDocument WHERE VersionId IN (SELECT id FROM #Versions)
-- Drop temp tables
DROP TABLE #Versions
DROP TABLE #Nodes
-- Reindex tables
DBCC DBREINDEX (cmsPropertyData)
DBCC DBREINDEX (cmsPreviewXml)
DBCC DBREINDEX (cmsContentVersion)
DBCC DBREINDEX (cmsDocument)
DBCC DBREINDEX (cmsContentXml)
DBCC DBREINDEX (umbracoDomains)
DBCC DBREINDEX (umbracoUser2NodePermission)
DBCC DBREINDEX (umbracoNode)
DBCC DBREINDEX (cmsContent)
I hope that helps.
Thanks Dan, now the audit trail is up front in the Info tab, this is really useful to hide the fact I've recycled a site!
Do you have a one for Umbraco 8, please?
Great cheers, I'll try it when I get home tonight. Fingers crossed it brings it down in size. The hosting company said they tried to compress it but it didn't go down by much.
It's a small site, just a blog so was surprised when I was told I've pushed up to 500Mb!
would you know if this will work on version 7.5.14?
I've almost finished importing a site and would like to remove the audit trail which was built up during the import.
It should do. I use an updated version for an Umbraco database clean script. You can find it on Github.
Hope that helps,
I can't even remember if I ran this script now :) I guess I must have as the site has been running fine ever since :)
hi all, just for informaiton, I tried this on Umbraco Cloud, on version 7.5.14, and it worked well. We had timeout problems with database restore, and transfering content.
The entire solution post can be found here.
Thank you Dan!
I've just tried it out on one of our test sites and it seems to have worked. I'm looking through the content on that site and can't see any errors.
Obviously, the Audit Trail is no longer there, and there is nothing to rollback to (of course, this was the purpose of the exercise in the first place).
The cmsPropertyData table has reduced from nearly 600,000 nodes to 171,000.
Thank you MuirisOG! I plan to try it out on my local machine running on 7.6.4 so we can have an updated SQL script. Will post back here as a follow up!
is it not an idea to create a smart version cleanup. that is only deleting not text related versions?
Thumbs up from me Owain.
This script just worked great on a couple of databases I had to clean up.
is working on a reply...
Write your reply to:
Image will be uploaded when post is submitted