Ah, ok. Should have thought of the 'path'. THanks Doug.

-- Nik

Ok, so if anyone else runs into this issue, herre's the script that I have created to 'delete' everything in the recycle bin. As Doug states above, this is not a sanctioned method so pleae backup all of your datasource(s) before attempting this:

-- 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


-- Delete all 'related' nodes and table contents...
delete from cmsContent where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)

delete from cmsContentXML where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)

delete from cmsDocument where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)

delete from cmsPropertyData where contentNodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)

-- delete the XML nodes....
delete from umbracoNode where path like '%-20%' and id!=-20

I think that recursive T-SQL will be more safe because I have just had situation in which path contains "-20" value but the parent of this node  isn't in recycle bin.

So how to update path column? :)

Best regards

@Nik, thank for posting this SQL, it's just solved my issue and saved me having to write it :)

Cheers,

Chris

Maybe a bit too late but isn't it safer and simpler to look for nodes with a parentID = -20 ?

Or am I missing something...

Regards,

Manos

 

Querying on ParentID = -20 vs path like '%-20%' and id!=-20 came back with different counts for me ???

I also needed to add a delete of the CMSTagRelationship table to the script above:

So mine looks like this:

-- 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

 

-- Delete all 'related' nodes and table contents...

delete from cmsContent where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)

delete from cmsContentXML where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)

delete from cmsDocument where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)delete from cmsPropertyData where contentNodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)

delete from cmsTagRelationship where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)

-- delete the XML nodes....

delete from umbracoNode where path like '%-20%' and id!=-20 

 

Greetings everyone, i had the same problem because i was migrating data from another database to umbraco, but i forgot to delete it permanently. Then in the Recycled bin I had like 6000 items. I tried the script that left Nik, but i had to do some changes, a i leave it :

delete from cmsPreviewXml where versionID in (select versionid from cmsContentVersion where ContentId in (select nodeId from cmsContent where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)))

delete from cmsContentVersion where ContentId in (select nodeid from cmsContent where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20))

delete from cmsPropertyData where contentNodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)

delete from cmsContentXML where nodeId in (select nodeid from cmsContent where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20))

delete from cmsDocument where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)

delete from cmsContent where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)

delete from umbracoNode where path like '%-20%' and id!=-20


/*
select * from cmsDocumentType where contentTypeNodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)

select * from cmsDataType where nodeid in (select id from umbracoNode where path like '%-20%' and id!=-20)

select * from umbracoUser2NodePermission where nodeid in (select id from umbracoNode where path like '%-20%' and id!=-20)



*/

By the way, it worked because i did not use tags,relations,members and permissions with the nodes.

Jorge

I executed the script of Dave, everything executes well, but on the last line I get an error message saying:

The DELETE statement conflicted with the SAME TABLE REFERENCE constraint "FK_umbracoNode_umbracoNode".

Any ideas?

My recycle bin is still not empty, because the last line won't execute I suppose?...

grts,

Kim

I have the same issue as kim any help?

Hi Tom,

I solved the issue by first removing the parent of the umbraco Node (=ParentId of current node) that had a relationship with the node I tried to remove using the SQL statement. When that node is removed, the foreign key conflict no longer occurs.

 

grts,

Kim

 

Thanks Kim!

I ended up doing the same! but unfortunately now when I add things to the recycle bin and try and delete im getting an error response from the web service call to remove a node:

 

Is functionality exposed through the API in version 5 for empyting or deleting items by their id from the recycle bin?

I can confirm the script bellow will delete all the bin items, got the error above 1st by using @nik script then tried @jorge script and it worked fine. this worked on an umbraco 4.11.4 installation;

delete from cmsPreviewXml where versionID in (select versionid from cmsContentVersion where ContentId in (select nodeId from cmsContent where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)))

delete from cmsContentVersion where ContentId in (select nodeid from cmsContent where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20))

delete from cmsPropertyData where contentNodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)

delete from cmsContentXML where nodeId in (select nodeid from cmsContent where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20))

delete from cmsDocument where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)

delete from cmsContent where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)

delete from umbracoNode where path like '%-20%' and id!=-20

Thanks @denford this worked for me. I'd say that 9 out of 10 times I am unable to empty the recycle bin. The UI offers no feedback and the underlying error you can see in your Firebug console provides nothing useful. The solution is a bit of an extreme measure but unfortauntely seems to be the only viable solution at the moment as running PathFix doesn't seem to overcome the error.

Cheers, Simon

in version 6.0.5, instead of deleting the contents in the bin, it deletes everything but, so the entire site has gone.
mysql db has the nodes and content (maybe previous versions), but the umbraco interface says nodes dont exsist and so no publish occurs.

 

 

 

I've used the Nik Wahlberg post to create this SQL Server Job to clean the recycle bin.

Hope its useful.

--select * From umbracoNode where path like '%-20%' and id!=-20

create procedure EmptyRecycleBin

as 

begin

 

declare @nodesToDelete table(id int, lev int)

declare @treeLevel int

declare @previousLevel int;

declare @rowsInserted int;

set @rowsInserted = 1;

set @treeLevel = 0;

set @previousLevel = 0;

 

insert into @nodesToDelete (id, lev)

SELECT DISTINCT id, @treeLevel FROM umbracoNode (nolock) where path like '%-20%' and id!=-20 and parentID = '-20'

 

set @treeLevel = @treeLevel + 1;

 

while @rowsInserted > 0

begin

SET ROWCOUNT 0

insert into @nodesToDelete (id, lev)

SELECT DISTINCT id, @treeLevel FROM umbracoNode (nolock) where parentID in (select id from @nodesToDelete where lev = @previousLevel)

set @rowsInserted = @@ROWCOUNT;

print @rowsInserted;

set @previousLevel = @treeLevel;

set @treeLevel = @treeLevel + 1;

end

 

declare @currId int

declare @currLevel int

declare currentDeletionCursor cursor for select * from @nodesToDelete order by lev desc

open currentDeletionCursor;

FETCH NEXT FROM currentDeletionCursor INTO @currId, @currLevel;

WHILE @@FETCH_STATUS = 0

begin

BEGIN TRY

begin tran;

delete from cmsPreviewXml where nodeId in (@currId)

delete from cmsContentVersion where contentId in (@currId)

delete from cmsDocument where nodeId in (@currId)

delete from cmsContentXML where nodeId in (@currId)

delete from cmsContent where nodeId in (@currId)

delete from cmsPropertyData where contentNodeId in (@currId)

delete from umbracoRelation where childId in (@currId)

delete from umbracoRelation where parentId in (@currId)

delete from umbracoUser2NodePermission where nodeId in (@currId)

DELETE FROM umbracoNode WHERE id = @currId;

commit;

END TRY

BEGIN CATCH

rollback;

END CATCH;

FETCH NEXT FROM currentDeletionCursor INTO @currId, @currLevel;

end

CLOSE currentDeletionCursor;

DEALLOCATE currentDeletionCursor;

end

@David - this seemed to work for me.

Umbraco v4.11.3

Database had appeared empty 'Empty recycle bin' said 1056 items.

Ran script [after backing up, of course]. took circa 12 mins to complete. nodes are gone, deleting still functions, everything seems good.

many thanks

From the first response I added a couple of other tables and it worked for me

-- 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

-- Delete all 'related' nodes and table contents...

delete from cmsPreviewXml where  nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)

delete from cmsContentVersion where  ContentId in (select id from umbracoNode where path like '%-20%' and id!=-20)

delete from cmsContent where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)

delete from cmsContentXML where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)

delete from cmsDocument where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)

delete from cmsPropertyData where contentNodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)

delete from umbracoDomains where domainRootStructureID IN (select id from umbracoNode where path like '%-20%' and id!=-20)

delete from cmsTagRelationship where nodeId IN (select id from umbracoNode where path like '%-20%' and id!=-20)

delete from umbracoUser2NodePermission where nodeId IN (select id from umbracoNode where path like '%-20%' and id!=-20)

delete from umbracoNode where path like '%-20%' and id!=-20

is working on a reply...