Purging older revisions from SQL database

Posted by Community Admin on 04-Aug-2018 19:37

Purging older revisions from SQL database

All Replies

Posted by Community Admin on 05-Jul-2012 00:00

This is related to my other post about migrating SQL data, but using a different approach.

I am about to deploy a project for a client. My database is now 263 MB and it has a lot of revision data from the initial design process. I need a way to purge all revisions except the current ones from the database.

Here is an old KB article at http://www.sitefinity.com/devnet/kb/sitefinity-3-x/how-to-reduce-the-project-database-size.aspx, but it applies to 3.x and I am running 5.x. In fact, none of the mentioned tables exist in my schema.

Bottom line: how can I purge all previous revisions? Can it be done directly through SQL?

Posted by Community Admin on 05-Jul-2012 00:00

Indeed... I recently launched a new site, and this was a real hassle to do manually.

I'd like to be able to have a clear & reset utility that purged all previous versions and reset the currently published version to V1.0

Launching a site where the only available revision is V87.0  just raises lots of questions, and makes me look like a goose -- yeah, I probably am a goose for needing that many revisions to get it right, but the customer doesn't need to know that - LOL.

Posted by Community Admin on 06-Jul-2012 00:00

Hello Steve,

Here is some sample code which you can run to purge the revision history for all pages for a given time period.

// getting all the pages here.
var pageMan1 = PageManager.GetManager();
IQueryable<PageNode> pages = pageMan1.GetPageNodes();
  
// getting the history manager.
var vers1Manager = VersionManager.GetManager();
foreach (PageNode page in pages)

    if (vers1Manager.GetItemVersionHistory(page.PageId).Count >= _numOfRevisions)
    
        var provider = VersionManager.GetManager().Provider;
        IQueryable<Change> result = from c in provider.GetChanges()
                                    where c.Parent.Id == page.PageId && c.LastModified < DateTime.Now//.AddMonths(-NumberOfMonths)
                                    select c;
  
        IQueryable<Change> result1 = from c in provider.GetChanges()
                                    where c.Parent.Id == page.PageId && c.LastModified > DateTime.Now//.AddMonths(-NumberOfMonths)
                                    select c;
        // inform the user of the number of records affected.
        this.lblResult.Text = result.Count() + " record(s) affected.";
          
        //delete all the revisions that are older than XX months.
        vers1Manager.TruncateVersions(page.PageId, DateTime.Now);
    
vers1Manager.SaveChanges();
pageMan1.Dispose();
vers1Manager.Dispose();

The code assumes that you have a label called lblResult and  You can modify the code to suit it to your needs.

Regards,
Victor Velev
the Telerik team
Do you want to have your say in the Sitefinity development roadmap? Do you want to know when a feature you requested is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items

Posted by Community Admin on 23-Sep-2012 00:00

hello, except the method above, still have the SQL method similar to 3.x?

Posted by Community Admin on 24-Sep-2012 00:00

I had to create a User Control that had a button on it and two settings. The settings were how many pages to leave and how old a page has to be before being purged. I then added the User Control to the Modules bar and now the User clicks on the button every now and then to purge old pages. The code is a modification of the above. Works fine and it does cut the size nicely when deleting loads. An automatic feature though would be very helpful.

Posted by Community Admin on 27-Sep-2012 00:00

We have strict retention guidelines that dictate the length of time a page must be kept before it is destroyed. It would be nice to get the script working so that any page or revision would be purged if, for example, it has not been published within the past year. Is this something others need also, or is that angle unique?

Posted by Community Admin on 13-Jan-2013 00:00

Hi all,

My requirement is that I want to delete frontend page revisions older than a variable date, and prevent the last published version from ever being deleted. I'm posting the code below, which seems to work fine for me; perhaps someone finds it handy. As this is quite a powerful feature which can seriously mess things up, I would also like to ask if the code is doing this in a safe manner.

VB.NET:

Function DeletePageRevisions(RemoveOlderThanDate As Date) As Long
    Dim ReturnValue As Long = 0
    Dim VersionManager As Telerik.Sitefinity.Versioning.VersionManager = Telerik.Sitefinity.Versioning.VersionManager.GetManager()
    ' Get all frontend pages.
    Dim FrontEndPageNodes As IQueryable(Of Telerik.Sitefinity.Pages.Model.PageNode) = Telerik.Sitefinity.App.WorkWith.Pages.LocatedIn(Telerik.Sitefinity.Fluent.Pages.PageLocation.Frontend).Get()
 
    ' Only proceed if a date has been selected.
    If (RemoveOlderThanDate > Date.MinValue) Then
        ' Examine all frontend pages.
        For Each Page As Telerik.Sitefinity.Pages.Model.PageNode In FrontEndPageNodes
            ' Get all revisions of this page.
            Dim PageRevisions As System.Collections.Generic.IList(Of Telerik.Sitefinity.Versioning.Model.Change) = VersionManager.GetItemVersionHistory(Page.PageId)
 
            ' Examine all revisions of this page.
            For Each PageRevision As Telerik.Sitefinity.Versioning.Model.Change In PageRevisions
                ' Do not delete the last published version of the page.
                If (Not PageRevision.IsLastPublishedVersion) Then
                    ' Delete all page revisions older then the provided date.
                    If (PageRevision.LastModified < RemoveOlderThanDate) Then
                        VersionManager.DeleteChange(PageRevision.Id)
                        ReturnValue += 1
                    End If
                End If
            Next
        Next
 
        ' Save the changes.
        VersionManager.SaveChanges()
        VersionManager.Dispose()
    End If
 
    Return ReturnValue
End Function

C#:

public long DeletePageRevisions(System.DateTime RemoveOlderThanDate)
    long ReturnValue = 0;
    Telerik.Sitefinity.Versioning.VersionManager VersionManager = Telerik.Sitefinity.Versioning.VersionManager.GetManager();
    // Get all frontend pages.
    IQueryable<Telerik.Sitefinity.Pages.Model.PageNode> FrontEndPageNodes = Telerik.Sitefinity.App.WorkWith.Pages.LocatedIn(Telerik.Sitefinity.Fluent.Pages.PageLocation.Frontend).Get();
 
    // Only proceed if a date has been selected.
    if ((RemoveOlderThanDate > System.DateTime.MinValue))
        // Examine all frontend pages.
        foreach (Telerik.Sitefinity.Pages.Model.PageNode Page in FrontEndPageNodes)
            // Get all revisions of this page.
            System.Collections.Generic.IList<Telerik.Sitefinity.Versioning.Model.Change> PageRevisions = VersionManager.GetItemVersionHistory(Page.PageId);
 
            // Examine all revisions of this page.
            foreach (Telerik.Sitefinity.Versioning.Model.Change PageRevision in PageRevisions)
                // Do not delete the last published version of the page.
                if ((!PageRevision.IsLastPublishedVersion))
                    // Delete all page revisions older then the provided date.
                    if ((PageRevision.LastModified < RemoveOlderThanDate))
                        VersionManager.DeleteChange(PageRevision.Id);
                        ReturnValue += 1;
                    
                
            
        
 
        // Save the changes.
        VersionManager.SaveChanges();
        VersionManager.Dispose();
    
 
    return ReturnValue;

Of course I've made sure that only Administrators can activate this function.

Posted by Community Admin on 20-Jan-2013 00:00

Hello,

I would recommend this free module - Wipe Deleted Versions Module

It allows you to wipe old version data using a nice UI, instead of using SQL queries to delete records directly from the database.

Posted by Community Admin on 20-Jan-2013 00:00

*Duplicate post*

Posted by Community Admin on 22-Jan-2013 00:00

*Duplicate post*

Posted by Community Admin on 11-Mar-2014 00:00

Any chance we are going to see a maintance module in 7.1+ roadmap.

 I just had a database outgrow the space. Some pages had 140 history versions.

 It really would be nice if you could set the number of max history versions to be saved.

 

Markus

Posted by Community Admin on 14-Mar-2014 00:00

Hello Markus,

Revision history settings are not scheduled for the 7.0 but there is logged a feature request in our Feedback portal, you could vote it. Most required features are scheduled with priority for development.

For the time being the database could be cleared by using the proposed solution in that forum post.

Regards,
Svetoslav Manchev
Telerik

 
Do you want to have your say in the Sitefinity development roadmap? Do you want to know when a feature you requested is added or when a bug fixed? Explore the Telerik Sitefinity CMS Ideas&Feedback Portal and vote to affect the priority of the items
 

Posted by Community Admin on 14-Mar-2014 00:00

That feature request was submitted in 2010! It's a valid request, one that would be beneficial for all website owners. It's time to implement!

Posted by Community Admin on 14-Mar-2014 00:00

Dear Arno

 Its not the oldest feature request or the once who make the most sense or the once needed by clients the most that get on the roadmap.

 It's like google indexing. Everyone tells you how it works but no one really knows :-)

 Sure hope to see that in before 8.0 is planned.

Markus

Posted by Community Admin on 14-Mar-2014 00:00

Yeah, we've been over this so many times now, that I've pretty much given up thinking Telerik are in any way interested in a feedback system that works.

But to vent about it one last time... honestly, who spends their time browsing such a site, in the hope that there is something there you consider worthy of voting for? It's a crazy notion that such a dysfunctional process is the basis for implementing changes.

At the very least, Telerik should take a leaf from the book of companies like GFI and implement a weekly email update to registered subscribers:

Sitefinity Ideas - This Week in Ideas & Feedback Portal

That way, people get sent a list of new/updated ideas posted during the last week, and can see at a glance if there is something there that they should vote for, rather than the current process of relying on people to visit the site in their "spare" time, and assume they'll stumble over someone else's idea and vote on it.

I was just reading the GFI ideas update this morning, and voted on one of them. I would never in a million years, have visited their ideas website, and searched for this, but when they told me about it... hey, that's a good idea, I'll vote for that!

Posted by Community Admin on 15-Mar-2014 00:00

+1 to that approach. It would be a major improvement. The number of votes would then at least reflect the actual interest of users.

Posted by Community Admin on 15-Mar-2014 00:00

As I also said, I think that should be the very LEAST Telerik provide, in implementing a working feedback/ideas/tracking portal.

I'd actually like to see it go much further, providing weekly updates on all items (e.g. bugs, requests, ideas, etc) for both new and changed status. i.e. what's new, what's confirmed, what's scheduled, what's fixed/implemented, what's trending, etc - with subscription options to various categories.

The point is, that without a weekly notification service, there is no reasonable way to know what is (or more likely isn't) happening on this portal, and it should be painfully obvious to even the most casual of observers, that a system reliant on people visiting a site and trawling through a database, in the hope of finding an idea/bug/etc that happens to meet their own problem description, is totally unrealistic and simply won't work. It actually looks more like lip-service than any real interest in feedback and user input.

Clearly, this is not a solution to all shortcomings of the feedback/ideas/tracking portal, and I don't imagine that there is any perfect system for finding reported bugs etc, but at least it gives users a heads-up on current activity, which is more than we have now.

Posted by Community Admin on 17-Mar-2014 00:00

Dear MB

You can take the weekly internal builds to see what has been fixed.

I don't think that the feedback system would work. There are so many open issues and such little that seem to be worked on.

The only thing that would help is to focus 80% on bug fixing for the next three verions to cut the number down. No there are so many that you would not know where to start

It feels like one of those simulation games where your population grows faster then you can produce food for them.

 Great thing come with 7.0 and again I am sure by the time we reach 8.0 we will see some more major improvements for us and our clients.

Markus

This thread is closed