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 382 posts 1124 karma points MVP 2x 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 415 posts 1970 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 12 posts 54 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!

  • Phil Carney 1 post 71 karma points
    Aug 13, 2019 @ 09:44
    Phil Carney
    0

    Hi Dan,

    Do you have a one for Umbraco 8, please?

    Kindest,

    Phil

  • George Phillipson 61 posts 176 karma points
    Oct 09, 2019 @ 23:47
    George Phillipson
    0

    Hi Phil

    Not sure if you found how to remove history in Umbraco 8, but I just do the following and it seems OK.

    delete [cmsDefaultUmbraco].[dbo].[umbracoLog]
    SELECT TOP 1000 [id]
          ,[userId]
          ,[NodeId]
          ,[entityType]
          ,[Datestamp]
          ,[logHeader]
          ,[logComment]
          ,[parameters]
      FROM [cmsDefaultUmbraco].[dbo].[umbracoLog]
    

    George

  • Owain Williams 382 posts 1124 karma points MVP 2x 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 378 posts 1276 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 415 posts 1970 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 382 posts 1124 karma points MVP 2x 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 62 posts 306 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 378 posts 1276 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 62 posts 306 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 31 posts 165 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?

  • David Armitage 190 posts 764 karma points
    Feb 04, 2019 @ 10:13
    David Armitage
    1

    Thumbs up from me Owain.

    This script just worked great on a couple of databases I had to clean up.

  • John Bergman 431 posts 984 karma points
    Aug 29, 2019 @ 06:21
    John Bergman
    1

    This is awesome! Anyone tried it with 7.14 or 7.15?

  • Owain Williams 382 posts 1124 karma points MVP 2x c-trib
    Sep 04, 2019 @ 08:49
    Owain Williams
    0

    Not tried it but I'd imagine it should work without any issues.

    If you do try it, please post here and let us know how it went.

    O.

  • Asembli 28 posts 124 karma points
    Sep 04, 2019 @ 16:27
    Asembli
    0

    Hi,

    I tried on 7.13.x and 7.14.x (suppose on 7.15 will be the same) and here is the result:

    Msg 4701, Level 16, State 1, Line 3
    Cannot find the object "umbracoUser2NodePermission" because it does not exist or you do not have permissions.
    Msg 4701, Level 16, State 1, Line 5
    Cannot find the object "umbracoUserLogins" because it does not exist or you do not have permissions.
    
    (20 rows affected)
    
    (128 rows affected)
    
    (128 rows affected)
    
    (941 rows affected)
    
    (128 rows affected)
    
    (128 rows affected)
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    Msg 2501, Level 16, State 45, Line 65
    Cannot find a table or object with the name "umbracoUser2NodePermission". Check the system catalog.
    

    I didn't investigate at which version data-model has changed.

    Regards,

    /Asembli

  • Asembli 28 posts 124 karma points
    Sep 04, 2019 @ 18:53
    Asembli
    1

    But you can try also with this block of statements:

    USE [YourDB]
    GO
    
    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)
    GO
    
    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)
    GO
    
    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)
    GO
    
    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)
    GO
    
    DELETE FROM umbracoLog
    GO
    
    TRUNCATE TABLE umbracoLog
    GO
    
    ALTER DATABASE YourDB
    SET RECOVERY SIMPLE
    GO
    DBCC SHRINKFILE (YourDB_log, 1)
    GO
    ALTER DATABASE YourDB
    SET RECOVERY FULL
    

    (last blocks are, if you have your db in full recovery mode)

    This works for me also on 7.14.x

    Regards,

    /Asembli

Please Sign in or register to post replies

Write your reply to:

Draft