Hi Wctc,

I'm not entirely sure what you asking / suggesting? Would you mind clarifying?

If you are just showing how to do it via SQL statement, this is already freely available in the sourcecode which you can find here:

http://unversion.codeplex.com/SourceControl/changeset/view/3e7948bad71b#ApplicationBase.cs

Many thanks

Matt

Hi Matt,

I would like to show how to remove all old content version without publishing a node.

Hi Wtct,

Ahh, ok, that's cool. You may want to look at adding it to the wiki though, as this forum is mainly for bugs, feedback and suggestions for the UnVersion package itself, so may end up getting a bit lost.

http://our.umbraco.org/wiki

I'd also suggest taking a look at the source of UnVersion as there seems to be a couple of items you've missed out that I would recommend adding, mainly cleaning up the cmsPreviewXml table, and ensuring the Published / Newest versions are not deleted (deleting these will cause problems).

The reason this package was created in the first place was really to ensure all loose ends are cleaned up, and making sure the data structure remains stable, so would hate for somebody to accidentaly break their install.

Many thanks

Matt

Thanks for this:

"mainly cleaning up the cmsPreviewXml table, and ensuring the Published / Newest versions are not deleted (deleting these will cause problems)"

Best regards!

wtct

Hi!

I prepared new SQL query version :)

I hope it is good version :) Yes I remember about cmsPreviewXm but it is similary.

DELETE 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)   
)

GO

DELETE FROM cmsContentVersion
WHERE VersionId IN (SELECT CV.versionId FROM cmsContentVersion CV LEFT JOIN cmsDocument D ON D.versionId = Cv.versionID WHERE D.versionID IS NULL)

GO

DELETE FROM cmsPropertyData
WHERE VersionId IN (SELECT DISTINCT PD.versionId FROM cmsPropertyData PD LEFT JOIN cmsDocument D ON D.versionId = PD.versionID WHERE D.versionID IS NULL)

You wish ! Before posting stupid SQL commands test locally !

So what is wrong with the second posted query?

It delete all media properties.... And had to restore 3 databases as I saw this lately !

If you're just looking to remove old versions periodically then F.A.L.M housekeeping can do this amongst other features (clearing and viewing logs etc.)

http://our.umbraco.org/projects/backoffice-extensions/falm-housekeeping

Rich

FALM Housekeeping (Error message with can something like can not find 0000000000000000 node id) ... Not Working ! Unversion not working... on 4.7 ! And tryed on 3 different implementations... with databases that are growing fast, I have a 2 month old site (40 subsites) where the DB is up to 1 GB 2, and could be reduced to +/- 250 MB

I think that you can fix my query because it is very simple.

USE [UmbracoDev]
GO
/****** Object:  StoredProcedure [dbo].[DeleteHistory]    Script Date: 09/20/2011 11:20:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER 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 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

Voila... Lazy boy... Here some good SQL practice !

So we are using your first query to save the VersionID's to delete into a temporary table.

And then we delete from each table the VersionIDs found in the #tmp table.

USE [UmbracoDev]
GO
/****** Object:  StoredProcedure [dbo].[DeleteHistory]    Script Date: 09/20/2011 11:20:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER 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 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

I agree, FALM Housekeeping seemed like a great idea but as time passes, it has caused more problems than it fixes.  It times out and then leaves a trail of orphan records in its path!  And it does not appear to support Umbraco 6.x and above.

is working on a reply...