Use this SQL script:

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

Btw, it's got something to do with corrupt nodes (node which is in the recycle bin (path property), but has a parent which is not in the recycle bin (parent property))

yes but i dont want to use a script - i want to use the umbraco functionallity on rightclick to delete this items.

I am not allowed to run direct database scripts

I think better way should be to solve the issue so deletion is possible via umbraco backend

Regards

Ofcourse you want to do it with the umbraco front-end, and we all want peace and happiness in the world as well... ;-)

Some things are just there and can't be fixed the 'easy way'. This is one of them. Your DB is corrupted when it comes to the recycle bin. You want to fix it? Use the SQL script (afaik there's no other option). Don't want to run the script? I'm afraid you're stuck with a corrupted recycle bin...

Sorry mate, been there done that...

Ps. Also I think most of the times these corruptions are caused by custom code (if not always).

I agree with Kipusoep

Your recycle bin issues are most likely due to ghost nodes. You have db entries in one or more tables which do not have their relations in other tables.

Something has gone wrong and the easiest way to fix this is via sql.

If you don't have db access, I think you can do something really hacky and put the sql in a razor script. Get a connection to the db via normal c#. Although I would never do this myself.

Ok we have tried your script but we are getting constraint violations.

The DELETE statement conflicted with the REFERENCE constraint "FK_cmsContentVersion_cmsContent".
The DELETE statement conflicted with the REFERENCE constraint "FK_umbracoDomains_umbracoNode".

Try this

begin transaction

-- 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 cmsDocument 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 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 the XML nodes...

DELETE FROM umbracoDomains WHERE domainRootStructureID IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20)

DELETE FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20

rollback transaction

ps. let us know how many nodes where ACTUALLY in exitence which needed to be deleted :)

Still get an error:

The DELETE statement conflicted with the REFERENCE constraint "FK_umbracoRelation_umbracoNode".

There are 242 files i am not able to delete

I dont have an umbraco db on me at the moment but i'm pretty sure that table is like the rest...You just need to copy one of the other sql lines and replace the table name with umbracoRelation, and replace the column name with the correct one in the relation table

ps. replace rollback transaction with commit transaction when you're confident it all works.

i just looked into the umbraco bin folder. It is empty now but your script has produced some errors.

I have to add a new line?

Sorry i am really new to umbraco :-(

Along with the other lines of sql you need this line:

DELETE FROM umbracoRelation WHERE Id IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20)

before this line:

DELETE FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20

Thanks Anthony,

Now i am getting this error:

The DELETE statement conflicted with the REFERENCE constraint "FK_umbracoRelation_umbracoNode".

Hi,

I think you need to delete records in the relation table which are 'related' on the parentId and/or the childId. So Anthony's DELETE statement actually needs to be 2 statements:

DELETE FROM umbracoRelation WHERE parentId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20)

DELETE FROM umbracoRelation WHERE childId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20)

Below is my complete script:

begin transaction
-- 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 cmsDocument 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 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 umbracoRelation where parentId in (select id from umbracoNode where path like '%-20%' and id!=-20)
delete from umbracoRelation where childId in (select id from umbracoNode where path like '%-20%' and id!=-20)

-- delete the XML nodes....
delete from umbracoDomains WHERE domainRootStructureID in (SELECT id FROM umbracoNode WHERE path like '%-20%' and id != -20)
delete from umbracoNode where path like '%-20%' and id!=-20
commit transaction

Hope this helps

Hi Vincent,

Your script works fine - thanks a lot

I hope until now i am able to delete whole recycle bin via umbraco and do not have to delete it directly in the database

is working on a reply...