Umbraco Fix – Empty recycle bin using SQL if backend fails

Updated thanks to jeffreypriebe

-- Uncomment below to verify the number of nodes returned is the same as the number of nodes that is in the Recycle Bin
-- SELECT * FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20

--Setup the temporary table
CREATE TABLE #temp ([id] int);
INSERT #temp select id from umbracoNode where path like '%-20%' and id!=-20

-- Delete all 'related' nodes and table contents...
DELETE FROM cmsPreviewXml WHERE nodeId IN (SELECT id FROM #temp)
DELETE FROM cmsContentVersion WHERE contentId IN (SELECT id FROM #temp)
DELETE FROM cmsDocument WHERE nodeId IN (SELECT id FROM #temp)
DELETE FROM cmsContentXML WHERE nodeId IN (SELECT id FROM #temp)
DELETE FROM cmsContent WHERE nodeId IN (SELECT id FROM #temp)
DELETE FROM cmsPropertyData WHERE contentNodeId IN (SELECT id FROM #temp)
DELETE FROM umbracoUser2NodePermission WHERE nodeId IN (SELECT id FROM #temp)
DELETE FROM umbracoRelation  where parentId in (select id from #temp) or childId in (select id from #temp)

-- remove fk constraint so that umbracoNode can delete
ALTER TABLE umbracoNode DROP CONSTRAINT FK_umbracoNode_umbracoNode
-- delete the XML nodes...
DELETE FROM umbracoNode WHERE id in (SELECT id FROM #temp)
-- re add fk constraint to umbracoNode
ALTER TABLE umbracoNode WITH NOCHECK ADD CONSTRAINT FK_umbracoNode_umbracoNode
FOREIGN KEY (parentId)
REFERENCES umbracoNode (id)

--Cleanup temp
DROP TABLE #temp;

3 Comments

  1. Its it really useful??

    • Sometimes very …. !!! Umbraco recycle bin crashes on many items…

  2. Also may need to clean up relations with this before deleting from umbracoNode

    delete from umbracoUser2NodePermission where nodeId in (select nodeId from umbracoNode where path like ‘%-20%’ and id!=-20)

Leave a Reply

Your email address will not be published. Required fields are marked *

 

This site uses Akismet to reduce spam. Learn how your comment data is processed.