remove audit trail and version history

Owain Williams 479 posts 1410 karma points MVP 6x c-trib

Jan 18, 2016 @ 13:47

General

Umbraco 7

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,

Copy Link

Dan Lister 416 posts 1974 karma points c-trib

Jan 18, 2016 @ 13:57

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.

Copy Link

Chris Dixon 12 posts 55 karma points

Feb 14, 2018 @ 15:20

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!

Copy Link

Phil Carney 1 post 71 karma points

Aug 13, 2019 @ 09:44

Hi Dan,

Do you have a one for Umbraco 8, please?

Kindest,

Phil

Copy Link

George Phillipson 108 posts 287 karma points

Oct 09, 2019 @ 23:47

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

Copy Link

Karen Worth 47 posts 140 karma points

Dec 14, 2021 @ 15:59

Very helpful, thanks Dan

Copy Link

Owain Williams 479 posts 1410 karma points MVP 6x c-trib

Jan 18, 2016 @ 13:59

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!

Copy Link

MuirisOG 382 posts 1284 karma points

Jun 21, 2017 @ 16:28

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

Copy Link

Dan Lister 416 posts 1974 karma points c-trib

Jun 21, 2017 @ 21:42

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.

Copy Link

Owain Williams 479 posts 1410 karma points MVP 6x c-trib

Jun 22, 2017 @ 08:26

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.

Copy Link

Genc Kastrati 86 posts 401 karma points

Jul 04, 2017 @ 11:15

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!

Copy Link

MuirisOG 382 posts 1284 karma points

Jul 14, 2017 @ 16:23

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.

Copy Link

Genc Kastrati 86 posts 401 karma points

Jul 14, 2017 @ 16:45

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!

Copy Link

Johan Reitsma 67 posts 233 karma points

Jul 15, 2017 @ 07:51

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

Copy Link

David Armitage 505 posts 2073 karma points

Feb 04, 2019 @ 10:13

Thumbs up from me Owain.

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

Copy Link

John Bergman 483 posts 1132 karma points

Aug 29, 2019 @ 06:21

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

Copy Link

Owain Williams 479 posts 1410 karma points MVP 6x c-trib

Sep 04, 2019 @ 08:49

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.

Copy Link

Asembli 81 posts 254 karma points

Sep 04, 2019 @ 16:27

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

Copy Link

Asembli 81 posts 254 karma points

Sep 04, 2019 @ 18:53

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

Copy Link

is working on a reply...

Flag this post as spam?

remove audit trail and version history