Copied to clipboard

Flag this post as spam?

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


  • Craig Noble 41 posts 584 karma points c-trib
    Apr 13, 2017 @ 14:14
    Craig Noble
    1

    Deleting content from recycle bin more than x days old

    Just to document this SQL command.

    It deletes all recycle bin items that is older than x days old.

    USE [database_name]
    
    DECLARE @daysAgo INT = -30 /*number of days(negative) */
    
    /* the max date */
    DECLARE @maxDate DATETIME = getDate();
    SET @maxDate = DATEADD(dd, @daysAgo, getDate());
    
    DECLARE @nodesToDelete TABLE(id INT, updateDate DATETIME)
    
    /* get all nodes that have been deleted */
    INSERT INTO @nodesToDelete SELECT n.id, d.updateDate
        FROM umbracoNode AS n
        LEFT JOIN cmsContent AS c
      ON c.nodeId = n.id
      LEFT JOIN cmsDocument AS d
      ON d.nodeId = n.id
      WHERE n.path LIKE '%-20%' AND n.id != -20 
            AND d.newest = 1 AND d.updateDate < @maxDate
    
    SELECT * FROM @nodesToDelete
    
    BEGIN TRANSACTION;
    BEGIN TRY
            PRINT 'Deleting cmsPreviewXml'
            delete from cmsPreviewXml where versionID in (select versionid from     cmsContentVersion where ContentId in (select nodeId from cmsContent     where nodeId in (select id from @nodesToDelete)))
            PRINT 'Deleting cmsContentVersion'
            delete from cmsContentVersion where ContentId in (select nodeid     from cmsContent where nodeId in (select id from @nodesToDelete))
        PRINT 'Deleting cmsPropertyData'
        delete from cmsPropertyData where contentNodeId in (select id from @nodesToDelete)
        PRINT 'Deleting cmsContentXML'
        delete from cmsContentXML where nodeId in (select nodeid from cmsContent where nodeId in (select id from @nodesToDelete))
        PRINT 'Deleting cmsDocument'
        delete from cmsDocument where nodeId in (select id from @nodesToDelete)
        PRINT 'Deleting cmsContent'
        delete from cmsContent where nodeId in (select id from @nodesToDelete)
            /* may not be needed */
        PRINT 'Deleting umbracoUser2NodeNotify'
        delete from umbracoUser2NodeNotify where nodeId in (select id from @nodesToDelete)
        PRINT 'Deleting umbracoUser2NodePermission'
        delete from umbracoUser2NodePermission where nodeId in (select id from @nodesToDelete)
            /* end of optional stuff*/
        PRINT 'Deleting umbracoRelation'
        delete from umbracoRelation where childId in (select id from @nodesToDelete)
        PRINT 'Deleting umbracoRelation'
        delete from umbracoRelation where parentId in (select id from @nodesToDelete)
        PRINT 'Deleting umbracoNode children'
        delete from umbracoNode where parentID in (select id from @nodesToDelete)
        PRINT 'Deleting umbracoNode'
        delete from umbracoNode where id in (select id from @nodesToDelete)
        PRINT 'Comitting Transaction'
        COMMIT TRANSACTION;
        PRINT 'Transaction Committed'
    END TRY
    BEGIN CATCH
        SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS     ErrorMessage;  
        PRINT 'Error occured. See Results.  Rolling back.'
        ROLLBACK TRANSACTION;
        PRINT 'Transaction rolled back'
    END CATCH
    
  • Casper Thygesen 5 posts 85 karma points
    May 13, 2018 @ 19:57
    Casper Thygesen
    0

    Sweet. Do you also have one for media items?

Please Sign in or register to post replies

Write your reply to:

Draft