clean up umbraco database

Sherry Ann Hernandez 320 posts 344 karma points

Nov 09, 2011 @ 06:23

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

Copy Link

Rodion Novoselov 694 posts 859 karma points

Nov 09, 2011 @ 22:33

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

Copy Link

Sherry Ann Hernandez 320 posts 344 karma points

Nov 10, 2011 @ 16:17

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

Copy Link

Paul Merks 35 posts 100 karma points

Sep 07, 2015 @ 15:27

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.

Copy Link

Lee Kelleher 4026 posts 15836 karma points MVP 13x admin c-trib

Sep 07, 2015 @ 16:12

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

Copy Link

Hendy Racher 863 posts 3849 karma points MVP 2x admin c-trib

Sep 07, 2015 @ 15:36

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

Copy Link

Paul Merks 35 posts 100 karma points

Sep 07, 2015 @ 17:14

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?

Copy Link

Paul Merks 35 posts 100 karma points

Sep 07, 2015 @ 17:15

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

Copy Link

Paul Merks 35 posts 100 karma points

Sep 07, 2015 @ 17:15

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

Copy Link

is working on a reply...

Flag this post as spam?

Clean up umbraco Database