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
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.
Sweet. Do you also have one for media items?
For Umbraco 8 Use the below Code
is working on a reply...