Copied to clipboard

Flag this post as spam?

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


  • Michael Argentini 20 posts 130 karma points
    Jul 26, 2019 @ 21:15
    Michael Argentini
    0

    Database Cleanup

    Is anyone aware of any C# or SQL scripts to perform a database cleanup? Looking to compact the data set of things like old log entries, unused data types, unused doc types, old/unused content versions, etc.

  • Alex Skrypnyk 6131 posts 23950 karma points MVP 7x admin c-trib
    Jul 27, 2019 @ 12:19
    Alex Skrypnyk
    0

    Hello Michael

    Have a look at this post - https://our.umbraco.com/forum/umbraco-cloud/98324-clean-up-recycle-bin-with-75000-nodes

    Similar Question.

    Thanks, Alex

  • Paul Hill 1 post 71 karma points
    Dec 06, 2019 @ 02:28
    Paul Hill
    0

    Hello Michael

    Have a look at this post - https://our.umbraco.com/forum/umbraco-cloud/google street view98324-clean-up-recycle-bin-with-75000-nodes

    Similar Question.

    Thanks, Alex

    Thank Alex, It's very helpful!

  • Alex Skrypnyk 6131 posts 23950 karma points MVP 7x admin c-trib
    Jul 27, 2019 @ 12:21
    Alex Skrypnyk
    0

    Really nice script from Damiaan - remove log entries and clean up old versions and previews:

    -- Umbraco Clear Old Document Versions To Decrease Database Size And Improve Performance
    -- http://borism.net/2008/12/16/fixing-a-large-cmspropertydata-table-in-umbraco/
    DECLARE @createdDate Datetime = DATEADD(m, -1, getdate())
    
    -- dump logs
    -- TRUNCATE TABLE umbracolog -- faster if log table is very big and you don't need anything
    DELETE FROM umbracolog WHERE Datestamp < @createdDate
    
    
    -- clean up old versions
    DELETE FROM cmsPropertyData WHERE
        versionId NOT IN (SELECT versionId FROM cmsDocument WHERE updateDate > @createdDate OR published = 1 OR newest = 1) AND
        contentNodeId IN (SELECT DISTINCT nodeID FROM cmsDocument)
    DELETE FROM cmsPreviewXml WHERE
        versionId NOT IN (SELECT versionId FROM cmsDocument WHERE updateDate > @createdDate OR published = 1 OR newest = 1) AND
        nodeId IN (SELECT DISTINCT nodeID FROM cmsDocument)
    DELETE FROM cmsContentVersion WHERE
        versionId NOT IN (SELECT versionId FROM cmsDocument WHERE updateDate > @createdDate OR published = 1 OR newest = 1) AND
        ContentId  IN (SELECT DISTINCT nodeID FROM cmsDocument)
    DELETE FROM cmsDocument WHERE
        versionId NOT IN (SELECT versionId FROM cmsDocument WHERE updateDate > @createdDate OR published = 1 OR newest = 1) AND
        nodeId IN (SELECT DISTINCT nodeID FROM cmsDocument)
    
    --http://blog.dampee.be/post/2015/11/28/Remove-Old-versions-of-Umbraco-Previews.aspx
    delete
    --select *
    from cmsPreviewXml
    where versionId in (
        select cmsPreviewXml.versionId
        from cmsPreviewXml join cmsDocument on cmsPreviewXml.versionId=cmsDocument.versionId
    where cmsDocument.newest <> 1)
    

    Thanks,

    Alex

  • Michael Argentini 20 posts 130 karma points
    Jul 28, 2019 @ 00:28
    Michael Argentini
    0

    Thanks Alex, but I need one for Umbraco 8 (that's the category of my post actually).

  • S Rupa 8 posts 111 karma points
    Nov 24, 2019 @ 22:24
    S Rupa
    0

    Any chance you found a way to do this yet?

  • Mikael Axel Kleinwort 140 posts 484 karma points c-trib
    May 19, 2021 @ 14:22
    Mikael Axel Kleinwort
    0

    I also look for a way to do this automatically on Umbraco 8.

  • Thomas 315 posts 602 karma points c-trib
    May 16, 2022 @ 11:22
    Thomas
    0

    Anything new here ? :)

Please Sign in or register to post replies

Write your reply to:

Draft