Copied to clipboard

Flag this post as spam?

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


  • Anders Brohus 194 posts 475 karma points
    Jul 31, 2017 @ 09:18
    Anders Brohus
    0

    Unable to empty recycle bin

    Hi our! :)

    Currently got a problem where i can't empty my recycle bin, it just gives me an "Timeout error" with "500 (Internal Server Error)"

    The site did run Umbraco 7.5.6 so i tried to upgrade to Umbraco 7.5.14, but it didn't help.

    It's both content and media recycle bin that i can't empty.

    I'm trying to empty those since the database is using 2.2GB, and i have UnVersion installed :)

  • Alex Skrypnyk 6045 posts 23035 karma points MVP 5x admin c-trib
    Jul 31, 2017 @ 11:54
    Alex Skrypnyk
    100

    Hi Anders

    Maybe it will help you, really nice SQL query for clearing Umbraco database, it can reduce size drastically:

    https://gist.github.com/dampee/a8ead728165b16d49c00

    Thanks,

    Alex

  • Anders Brohus 194 posts 475 karma points
    Jul 31, 2017 @ 12:41
    Anders Brohus
    0

    Hi Alex,

    Thanks for your time! :)

    I will try run that script, currently i have tried this one,

    https://gist.github.com/genckastrati/0e7f7ae6156647e00a56eb5ef403166d

    But it didn't allowed me to empty the recycle bins :(

  • Alex Skrypnyk 6045 posts 23035 karma points MVP 5x admin c-trib
    Jul 31, 2017 @ 14:16
    Alex Skrypnyk
    0

    You are welcome, also can you check what is timeout for sql server operations?

    Try to check it with MSSQL management studio.enter image description here

    Hope it will help,

    Alex

  • GiuServ 4 posts 75 karma points
    Jan 04, 2018 @ 11:16
    GiuServ
    2

    For umbraco 7+ (currently last release 7.7.7) i made this script to empty both media and document recycle bin:

    DELETE FROM cmsPreviewXml WHERE  nodeId IN (SELECT id FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
    DELETE FROM cmsContentVersion WHERE  ContentId IN (SELECT id FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
    DELETE FROM cmsDocument WHERE nodeId IN (SELECT id FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
    DELETE FROM cmsTagRelationship WHERE nodeId IN (SELECT id FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
    DELETE FROM cmsContentXML WHERE nodeId IN (SELECT id FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
    DELETE FROM cmsContent WHERE nodeId IN (SELECT id FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
    DELETE FROM cmsPropertyData WHERE contentNodeId IN (SELECT id FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
    DELETE FROM umbracoDomains WHERE domainRootStructureID IN (SELECT id FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
    DELETE FROM umbracoUser2NodePermission WHERE nodeId IN (SELECT id FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
    DELETE FROM umbracorelation WHERE parentId IN (SELECT id FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
    DELETE FROM umbracorelation WHERE childId IN (SELECT id FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
    DELETE FROM umbracoredirecturl WHERE contentKey IN (SELECT uniqueID FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
    ALTER TABLE `umbraconode` DROP FOREIGN KEY `umbraconode_ibfk_1`; 
    DELETE FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20);
    ALTER TABLE `umbraconode` ADD CONSTRAINT `umbraconode_ibfk_1` FOREIGN KEY (`parentID`) REFERENCES `umbraconode`(`id`);
    

    Backup your DB before trying it.

  • Markus Johansson 1720 posts 4928 karma points c-trib
    Dec 06, 2018 @ 11:40
    Markus Johansson
    0

    Thanks for sharing this!

    I noticed some errors related to cmsContent what was about to be deleted but had a foreign key constrain to a media item, after emptying the media recycle bin from the backoffice this code worked great on Umbraco v7.12.3.

    Edit: Also for 7.8+ the umbracoUser2NodePermission-row can be changed to

    DELETE FROM umbracoUserGroup2NodePermission WHERE nodeId IN (SELECT id FROM umbracoNode WHERE (path LIKE '%-21%' AND id!=-21) OR (path LIKE '%-20%' AND id!=-20));
    

    // m

  • Alex Skrypnyk 6045 posts 23035 karma points MVP 5x admin c-trib
    Jul 23, 2019 @ 23:01
    Alex Skrypnyk
    0

    Is this script working for the latest Umbraco version?

  • DT 4 posts 24 karma points
    Jul 30, 2019 @ 11:07
    DT
    0

    Can anyone help with a version to Umbraco 8?

  • Tim Miller 32 posts 251 karma points
    Aug 13, 2021 @ 19:58
    Tim Miller
    0

    Here's a version that I use for v8.

    BEGIN TRAN
    
    -- I recommend figuring out what is causing any constraint issues instead of turning the constraint off, but thought I'd include it.  
    --ALTER TABLE umbracoNode NOCHECK CONSTRAINT FK_umbracoNode_umbracoNode_id
    
    DECLARE @Nodes TABLE (NodeId int)
    
    INSERT INTO @Nodes (NodeId)
        SELECT id
        FROM umbracoNode
        WHERE [path] like '%-20,%'
            AND id !=- 20
            -- Content recycle bin only
            AND nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972'
    
    delete from umbracoDomain where id in (select NodeId from @Nodes);
    delete from cmsContentNu where nodeId in (select NodeId from @Nodes)
    delete from umbracoRedirectUrl where contentKey in (select uniqueId from umbracoNode where id in (select NodeId from @Nodes))
    delete from cmsTagRelationship where nodeId in (select NodeId from @Nodes)
    delete from umbracoContentSchedule where nodeId in (select NodeId from @Nodes)
    delete from umbracoDocumentCultureVariation where nodeId in (select NodeId from @Nodes)
    delete from umbracoRelation where parentId in (select NodeId from @Nodes) or childId in (select NodeId from @Nodes)
    delete from umbracoUserGroup2NodePermission where nodeId in (select NodeId from @Nodes)
    delete from umbracoUserStartNode where startNode in (select NodeId from @Nodes)
    update umbracoUserGroup set startContentId = null where startContentId in (select NodeId from @Nodes)
    delete from umbracoPropertyData where versionId in (select id from umbracoContentVersion where nodeId in (select NodeId from @Nodes))
    delete from umbracoDocumentVersion where id in (select id from umbracoContentVersion where nodeId in (select NodeId from @Nodes))
    delete from umbracoContentVersion where nodeId in (select NodeId from @Nodes)
    delete from umbracoDocument where nodeId in (select NodeId from @Nodes)
    delete from umbracoContent where nodeId in (select NodeId from @Nodes)
    delete from umbracoNode where id in (select NodeId from @Nodes)
    delete from umbracoUser2NodeNotify where nodeId in (select NodeId from @Nodes)
    
    -- Any custom tables that you have that might need to be cleaned up too.
    
    DELETE FROM @Nodes
    
    --ALTER TABLE umbracoNode CHECK CONSTRAINT FK_umbracoNode_umbracoNode_id
    
    ROLLBACK TRAN
    

    Running this in a transaction allows you to see what is about to happen w/o actually deleting anything. Once you are satisfied with it just change that last line (ROLLBACK TRAN) to COMMIT TRAN

    I've also toyed with deleting only items that have been in the recycle bin > 1 month and doing this with a scheduled task. That would change your selection of the nodes to something like:

    SELECT id
    FROM umbracoNode u
        join umbracoLog l on l.NodeId = u.id
    WHERE u.[path] like '%-20,%'
        AND u.id !=- 20
        AND u.nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972'
        AND l.logHeader = 'Delete'
        AND l.Datestamp < DateAdd(month, -1, GetDate())
    

    If you do have constraint errors I've used a cursor to iterate over each of the nodes and run the delete statements on them individually. That helps find any nodes that are causing issues. I can probably provide that too if you need it.

  • Jesse Andrews 169 posts 657 karma points
    Jul 28, 2021 @ 20:38
    Jesse Andrews
    0

    I ran into this too, but I was able to work around it. There are a couple key things to note.

    1. Click recycle bin in navigation on the left to reach a list view that can be used to delete content in batches.
    2. Timeout errors can be caused by having too many versions of a node. A plugin like UnVersion can be used to remove old versions so that the node can be deleted.

    I've posted about this in more detail on another thread for this same issue. Feel free to look there for more details on how I resolved this.

  • Peter Kindberg 13 posts 83 karma points
    Aug 16, 2021 @ 10:04
    Peter Kindberg
    0

    Hi everyone,

    I've run into this problem in the past, and the most tedious way of doing it is to manually empty 5-10 nodes at a time. If there is a folder, empty only that folder.

    The reason being that the timeout will kick in for larger batches of cleaning.

    The solution proposed by Alex, to increase the database timeout, looks like a cool one, but I assume for very large batches it could take close to an hour.

    /Peter

Please Sign in or register to post replies

Write your reply to:

Draft