Sign in Register
Copied to clipboard

Flag this post as spam?

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


  • Arun 144 posts 407 karma points
    Feb 16, 2023 @ 12:40
    Arun
    0

    Can we truncate umbracoAudit table

    Hi Team,

    I was trying to shrink a DB and I found the umbracoAudit with 3GB of data. Im permitted to clear the rollback versions. With some queries, the version seems clear from the Umbraco node but still, I found this table with a huge amount of data.

    What's the purpose of the umbracoAudit table?
    Is it used for keeping the login records?
    Can we truncating that?

    Regards

    Copy Link
  • Dave Woestenborghs 3504 posts 12135 karma points MVP 9x admin c-trib
    Feb 16, 2023 @ 13:00
    Dave Woestenborghs
    0

    Hi Arun,

    The audit log table is used to show the history of a content item and maybe in other places as well.

    enter image description here

    I would suggest deleting records older than a certain date. For example only keep the ones from the last 6 months.

    Dave

    Copy Link
  • Arun 144 posts 407 karma points
    Feb 16, 2023 @ 14:07
    Arun
    0

    Thanks Dave,

    I was able to clear the table umbracolog 

    delete FROM umbracolog WHERE Datestamp < DATEADD(day, -1, GETDATE())    

    Also, umbracoContentVersion & umbracoDocumentVersion 

    SELECT cv.id
INTO #toDelete
FROM umbracoDocumentVersion dv
INNER JOIN umbracoContentVersion cv ON dv.id = cv.id
WHERE cv.[current] != 1 AND dv.published != 1 AND cv.VersionDate < DATEADD(day, -1, GETDATE()) 
DELETE FROM umbracoPropertyData WHERE versionId IN (select id from #toDelete)
DELETE FROM umbracoDocumentVersion WHERE id IN (select id from #toDelete)
DELETE FROM umbracoContentVersion WHERE id IN (select id from #toDelete) 
DROP TABLE #toDelete

    But the mentioned table umbracoAudit remains untouched. Now I have truncated that one and the site working without any issues.

    Is there any suggestions that will be helpful to reduce the DB size further?

    Thanks

    Copy Link
  • Dave Woestenborghs 3504 posts 12135 karma points MVP 9x admin c-trib
    Feb 17, 2023 @ 07:06
    Dave Woestenborghs
    100

    Hi Arun,

    Since 8.18 versions will be auto cleaned up in Umbraco. But if you are upgrading from a older version you will need to activate it : https://umbraco.com/blog/umbraco-818-release-candidate/?#history

    For cleaning up the audit table you could write a scheduled task.

    For V8 : https://our.umbraco.com/Documentation/Reference/Scheduling/

    For V9+ : https://docs.umbraco.com/umbraco-cms/reference/scheduling

    Dave

    Copy Link

  • This forum is in read-only mode while we transition to the new forum.

    You can continue this topic on the new forum by tapping the "Continue discussion" link below.

Please Sign in or register to post replies