Copied to clipboard

Flag this post as spam?

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


  • Sherry Ann Hernandez 320 posts 344 karma points
    Nov 09, 2011 @ 06:23
    Sherry Ann Hernandez
    0

    Clean up umbraco Database

    Hi,

    Is there a way to clean up the database by deleting old published documents and media? I want to trim down our database size because right now is already around 700mb.

    Sherry

  • Rodion Novoselov 694 posts 859 karma points
    Nov 09, 2011 @ 22:33
    Rodion Novoselov
    0

    Hi. Probably this link could be of help:
     http://our.umbraco.org/projects/website-utilities/unversion

  • Sherry Ann Hernandez 320 posts 344 karma points
    Nov 10, 2011 @ 16:17
    Sherry Ann Hernandez
    0

    Our database is around 110mb. Is this normal for 4000 pages?

  • Paul Merks 35 posts 100 karma points
    Sep 07, 2015 @ 15:27
    Paul Merks
    0

    My database is 25GB, thanks to the old versions. All the unversion tools seems to freeze or do not have any effect. Does anybody have some working SQL queries? I've been trying to clean the data since it became an unworkable Umbraco implementation.

  • Lee Kelleher 4020 posts 15802 karma points MVP 13x admin c-trib
    Sep 07, 2015 @ 16:12
    Lee Kelleher
    0

    Hi Paul,

    Following up on Hendy's SQL... here's a gist of a couple of SQL queries that I run on my sites...

    https://gist.github.com/leekelleher/7024860

    -- cmsContentVersion_Version_Count.sql
    SELECT
        ContentId,
        COUNT(ContentId) AS VersionCount
    FROM
        cmsContentVersion
    GROUP BY
        ContentId
    ORDER BY
        COUNT(ContentId) DESC
    ;
    

    ...and...

    -- umbracoLog_LogHeader_Count.sql
    SELECT
        logHeader,
        COUNT(logHeader) AS LogCount
    FROM
        umbracoLog
    GROUP BY
        logHeader
    ORDER BY
        COUNT(logHeader) DESC
    ;
    

    ...and...

    -- umbracoNode_nodeObjectType_Count.sql
    SELECT
        CASE
            WHEN nodeObjectType = '7A333C54-6F43-40A4-86A2-18688DC7E532' THEN 'ContentItemType'
            WHEN nodeObjectType = 'EA7D8624-4CFE-4578-A871-24AA946BF34D' THEN 'ROOT' 
            WHEN nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972' THEN 'Document'  
            WHEN nodeObjectType = 'B796F64C-1F99-4FFB-B886-4BF4BC011A9C' THEN 'Media' 
            WHEN nodeObjectType = '9B5416FB-E72F-45A9-A07B-5A9A2709CE43' THEN 'MemberType'
            WHEN nodeObjectType = '6FBDE604-4178-42CE-A10B-8A2600A2F07D' THEN 'Template'
            WHEN nodeObjectType = '366E63B9-880F-4E13-A61C-98069B029728' THEN 'MemberGroup'
            WHEN nodeObjectType = '10E2B09F-C28B-476D-B77A-AA686435E44A' THEN 'ContentItem'
            WHEN nodeObjectType = '4EA4382B-2F5A-4C2B-9587-AE9B3CF3602E' THEN 'MediaType'
            WHEN nodeObjectType = 'A2CB7800-F571-4787-9638-BC48539A0EFB' THEN 'DocumentType'
            WHEN nodeObjectType = '01BB7FF2-24DC-4C0C-95A2-C24EF72BBAC8' THEN 'RecycleBinContent'
            WHEN nodeObjectType = '9F68DA4F-A3A8-44C2-8226-DCBD125E4840' THEN 'Stylesheet'
            WHEN nodeObjectType = '5555DA4F-A123-42B2-4488-DCDFB25E4111' THEN 'StylesheetProperty'
            WHEN nodeObjectType = 'CF3D8E34-1C1C-41E9-AE56-878B57B32113' THEN 'RecycleBinMedia'
            WHEN nodeObjectType = '39EB0F98-B348-42A1-8662-E7EB18487560' THEN 'Member'
            WHEN nodeObjectType = '30A2A501-1978-4DDB-A57B-F7EFED43BA3C' THEN 'DataType'
            ELSE convert(nvarchar(50), nodeObjectType)
        END AS objectType,
        COUNT(nodeObjectType) AS nodeCount
    FROM
        umbracoNode
    GROUP BY
        nodeObjectType
    ORDER BY
        COUNT(nodeObjectType) DESC
    ;
    

    Cheers,
    - Lee

  • Hendy Racher 863 posts 3849 karma points MVP 2x admin c-trib
    Sep 07, 2015 @ 15:36
    Hendy Racher
    1

    Hi Paul,

    The following SQL might help (usual caveats - backup the database etc...)

      -- Create a temporary table for all documents which are published and not in the recycle bin
    CREATE TABLE #Nodes (id int)
    
    -- Delete all rows if the table already exists
    TRUNCATE TABLE #Nodes
    
    -- 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
    
    -- Create a temporary table for all versionId's to delete
    CREATE TABLE #Versions (id UniqueIdentifier)
    
    -- Delete all rows if it exists before
    TRUNCATE TABLE #Versions
    
    -- 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
    
    -- DELETE all versions from 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 cmsPropertyData WHERE VersionId IN (SELECT id FROM #Versions)
    DELETE FROM cmsContentVersion WHERE VersionId IN (SELECT id FROM #Versions)
    DELETE FROM cmsDocument WHERE VersionId IN (SELECT id FROM #Versions)
    

    HTH, Hendy

  • Paul Merks 35 posts 100 karma points
    Sep 07, 2015 @ 17:14
    Paul Merks
    0

    Ok, i deleted a lot of old propertydata, but the old versions still exists in cmsContentVersion. The CMS and Media got a lot faster now, but i've got the feeling there are still a lot of records that could be deleted?

  • Paul Merks 35 posts 100 karma points
    Sep 07, 2015 @ 17:15
    Paul Merks
    0

    Btw, my database backup is now down from 25 GB to 14 GB. Still a lot i my opinion.

  • Paul Merks 35 posts 100 karma points
    Sep 07, 2015 @ 17:15
    Paul Merks
    0

    Anyone has a script for deleting unused media items BTW? In database and on disk.

Please Sign in or register to post replies

Write your reply to:

Draft