Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at

  • mmaty 72 posts 175 karma points
    Mar 26, 2019 @ 13:12

    Delete old versions

    In Umbraco 7 we got used to delete old versions with some SQL statements. But these statements don't work anymore in Umbraco 8, since the database structure changed. I tried to use the DeleteVersions method of the ContentService, but it doesn't work:

        // Get all nodes with old versions
        static readonly string sqlForOldVersions = 
           @"SELECT distinct [nodeId] FROM[umbracoContentVersion] 
           where[current] <> 1";
        List<int> ids = new List<int>();
        using (var reader = handler.Execute( sqlForOldVersions, true ))
        while (reader.Read())
            ids.Add( reader.GetInt32( 0 ) );
        foreach (var id in ids)
            this.contentService.DeleteVersions( id, dt );

    It throws with the following error:

    The primary key value cannot be deleted because references to this key 
    still exist. [ Foreign key constraint name = 
      FK_umbracoDocumentVersion_umbracoContentVersion_id ]

    Does anybody have any insights regarding this issue?

  • Richard Soeteman 3629 posts 11066 karma points MVP
    Mar 26, 2019 @ 13:44
    Richard Soeteman

    If the contentservice is throwing that error I assume it's a bug in V8.

  • mmaty 72 posts 175 karma points
    Mar 27, 2019 @ 10:41

    There should definitely be a housekeeping functionality to delete old versions. The API only allows to delete old versions of a given content. But how do we determine, which contents have old versions?

    The SQL query in my sample code above is a quick hack.

    Why isn't there an API like

    DeleteAllContentVersionsBefore(DateTime dt)?

    This should have been part of Umbraco since the v4 days… ;-)

  • Remko 74 posts 194 karma points
    Aug 30, 2019 @ 15:06

    I stumbled upon this one as well.. Did you figure out how to fix this?

  • Steve Megson 99 posts 481 karma points c-trib
    Aug 30, 2019 @ 20:47
    Steve Megson

    That does look like a bug in v8. That foreign key was missing before, so it didn't matter which order things were deleted. I think it might also need a tweak to how DeleteVersions decides which versions can be deleted.

    For deleting old versions of all content, this SQL should work for v8

    INTO #toDelete
    FROM umbracoDocumentVersion dv
    INNER JOIN umbracoContentVersion cv ON =
    WHERE cv.[current] != 1 AND dv.published != 1 AND cv.VersionDate < 'xxxx-xx-xx'
    DELETE FROM umbracoPropertyData WHERE versionId IN (select id from #toDelete)
    DELETE FROM umbracoDocumentVersion WHERE id IN (select id from #toDelete)
    DELETE FROM umbracoContentVersion WHERE id IN (select id from #toDelete)
    DROP TABLE #toDelete

    Adding a ContentService.DeleteAllVersionsBefore(DateTime dt) would be nice, though that SQL only works for SQL Server. We'd need some other solution for SQL CE.

  • Michael Argentini 20 posts 125 karma points
    1 week ago
    Michael Argentini

    This is great. I've been looking for any kind of help with compacting an Umbraco 8 database; removing old versions, pruning the log, etc. If you happen to have any other "clean up" scripts to share please do!

Please Sign in or register to post replies

Write your reply to: