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 115 posts 240 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 ? 

     

     

  • Laurent Lequenne 115 posts 240 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

  • skiltz 500 posts 699 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

     

     

Please Sign in or register to post replies

Write your reply to:

Draft