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?

  • Sowndar M 54 posts 148 karma points
    Jun 20, 2023 @ 03:21
    Sowndar M
    0

    For Umbraco 8 Use the below Code

     var recycleBin = _contentService.GetPagedContentInRecycleBin(0, int.MaxValue, out var totalRecords);
            var olderThanDays = 300;
            olderThanDays = -olderThanDays;
            DateTime thresholdDate = DateTime.Now.AddDays(olderThanDays);
            var filterPost = recycleBin.Where(x => x.UpdateDate < thresholdDate);
            foreach (var contentItem in filterPost)
            {
                _contentService.Delete(contentItem);
            }
    
            return CurrentUmbracoPage();
    
  • This forum is in read-only mode while we transition to the new forum.

    You can continue this topic on the new forum by tapping the "Continue discussion" link below.

Please Sign in or register to post replies