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 ?
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 ?
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
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
is working on a reply...