Deleting content items from admin leaves old versions of the content in the database

[scleaver]

Version rc1-12811: I have deleted all the content using admin UI and then noticed that there are still thousands of version records for these delete content items in the database.

I thought ok I will delete the rows using an SQL query but they won't delete because of a constraint: The DELETE statement conflicted with the REFERENCE constraint "XXXX_ContentItemIndex". The conflict occurred in database "XXXX", table "dbo.XXXX_ContentItemIndex", column 'DocumentId'.

Seems not right that delete content remains in the database.

[ns8482e]

I guess content Item is soft delete.

[Piedone]

A content item removal is intentionally a soft delete in Orchard. While there is no similar hard delete surfaced, I think ISession.Delete(contentItem) should do the trick.

[scleaver]

Over time the DB is going to be littered with thousands of orphaned content item versions that have no real purpose - this doesn't seem like a great strategy and there is no easy way to clean them up either.

[EternityTeam]

I've had this problem for a long time

[Piedone]

If you never need those old versions for compliance reasons or to be able to restore them (with Audit Trail: #4620) then those are indeed clutter. We could surface the hard delete operation via e.g. IContentManager.Destroy(), and have a corresponding UI for that (also probably in Audit Trail as in O1).

Related: #6056

[Skrypt]

We need a content item trash can feature. That way we could clean up the database from some drafts/published content items manually ... audit trail apart.

[Piedone]

There will be a recycle bin in Audit Trail.

[jptissot]

Maybe we could also have a setting with the number of versions to keep for versionalble content ? And if a ContentItem is not versionable, we should do hard deletes!

[sebastienros]

There is a checkbox to prevent version already too.
And this issue is "by design" so not an issue IMO.
Other modules will provide ways to "destroy" these versions. We have also already talked about having a "Purge" action, even automatic, I assume there is an issue somewhere describing this.