Sign in Register
Copied to clipboard

Flag this post as spam?

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


  • Laurent Lequenne 123 posts 248 karma points
    Mar 10, 2013 @ 00:14
    Laurent Lequenne
    0

    Version history in umbraco 6

    Is there anything provided with this version, to clean or limit the history ? This history is really nasty if we are on large sites with a lot of content editing the DB size is just blowing up... And everybody knows that DB space are really expensive on hosting !

    If not, is there somewhere a store procedure that could help doing the job  ? Or some API available to effectively delete the versions of the DB ? 

     

     

    Copy Link
  • Laurent Lequenne 123 posts 248 karma points
    Mar 10, 2013 @ 12:16
    Laurent Lequenne
    2

    Bon... I did it myself :=) Here a stored procedure that can delete version history. Replace tmp.RowNum < 3 by the number of versions you want to keep.

     

    CREATE PROCEDURE [dbo].[DeleteHistory]

    AS

    BEGIN

     

    Select VersionID into #tmp FROM cmsDocument

    WHERE versionID NOT IN  (SELECT D.versionId FROM cmsDocument D 

    WHERE D.versionId IN (SELECT versionId FROM (SELECT CV.versionId, published, newest, RANK() OVER(ORDER BY CV.versionDate DESC) RowNum   

                                                      FROM cmsContentVersion CV JOIN cmsDocument DD ON CV.versionId = DD.versionId    

                                                                                                          WHERE DD.nodeId = D.nodeId) AS tmp     

                                  WHERE tmp.RowNum <= 3 OR tmp.published = 1 OR tmp.newest = 1)  )

                                  

     

    DELETE FROM cmsPreviewXml WHERE VersionId IN (select #tmp.VersionId from #tmp)

    DELETE FROM cmsContentVersion WHERE VersionId IN (select #tmp.VersionId from #tmp)

    DELETE FROM cmsPropertyData WHERE VersionId IN (select #tmp.VersionId from #tmp)

    DELETE FROM cmsDocument WHERE VersionId IN (select #tmp.VersionId from #tmp)

    END

    Copy Link
  • skiltz 501 posts 701 karma points
    Aug 08, 2013 @ 00:57
    skiltz
    0

    Thanks!  I just used this on a large site (took about 10 minutes to run query) deleted about 30,000 old versions.  

    Site still working as expected :) Did dummy run first and made backups.

    -Matthew

     

     

    Copy Link

  • This forum is in read-only mode while we transition to the new forum.

    You can continue this topic on the new forum by tapping the "Continue discussion" link below.

Please Sign in or register to post replies