Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Owain Williams 253 posts 776 karma points c-trib
    Jan 18, 2016 @ 13:47
    Owain Williams
    0

    remove audit trail and version history

    Hi! 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.

    Thanks,

  • Dan Lister 414 posts 1935 karma points c-trib
    Jan 18, 2016 @ 13:57
    Dan Lister
    1

    Hi Owain,

    Here is a SQL script that I use to clean an Umbraco database:

    TRUNCATE TABLE umbracoLog 
    GO
    TRUNCATE TABLE umbracoUser2NodePermission
    GO
    TRUNCATE TABLE umbracoUserLogins
    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 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)
    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)
    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.

  • Chris Dixon 10 posts 52 karma points
    Feb 14, 2018 @ 15:20
    Chris Dixon
    1

    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!

  • Owain Williams 253 posts 776 karma points c-trib
    Jan 18, 2016 @ 13:59
    Owain Williams
    0

    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!

  • MuirisOG 294 posts 999 karma points
    Jun 21, 2017 @ 16:28
    MuirisOG
    0

    Hi Dan

    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.

    Many thanks

    Muiris

  • Dan Lister 414 posts 1935 karma points c-trib
    Jun 21, 2017 @ 21:42
    Dan Lister
    2

    Hey Muiris,

    It should do. I use an updated version for an Umbraco database clean script. You can find it on Github.

    Hope that helps, Dan.

  • Owain Williams 253 posts 776 karma points c-trib
    Jun 22, 2017 @ 08:26
    Owain Williams
    0

    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 :)

    O.

  • Genc Kastrati 46 posts 269 karma points
    Jul 04, 2017 @ 11:15
    Genc Kastrati
    0

    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!

  • MuirisOG 294 posts 999 karma points
    Jul 14, 2017 @ 16:23
    MuirisOG
    0

    Hi Genc,

    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.

    Many thanks.

  • Genc Kastrati 46 posts 269 karma points
    Jul 14, 2017 @ 16:45
    Genc Kastrati
    0

    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!

  • Johan Reitsma 23 posts 126 karma points
    Jul 15, 2017 @ 07:51
    Johan Reitsma
    0

    is it not an idea to create a smart version cleanup. that is only deleting not text related versions?

Please Sign in or register to post replies

Write your reply to:

Draft