+1-888-365-2779
Try Now
More in this section

Forums / Developing with Sitefinity / Purging older revisions from SQL database

Purging older revisions from SQL database

18 posts, 0 answered
  1. Steve
    Steve avatar
    13 posts
    Registered:
    29 Apr 2011
    05 Jul 2012
    Link to this post
    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?
  2. MB
    MB avatar
    302 posts
    Registered:
    09 Jan 2005
    05 Jul 2012
    Link to this post
    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.
  3. Victor Velev
    Victor Velev avatar
    1345 posts
    Registered:
    06 Jul 2015
    06 Jul 2012
    Link to this post
    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
  4. Anthony
    Anthony avatar
    1 posts
    Registered:
    11 Jul 2012
    23 Sep 2012
    Link to this post
    hello, except the method above, still have the SQL method similar to 3.x?
  5. Andrei
    Andrei avatar
    10 posts
    Registered:
    16 Apr 2012
    24 Sep 2012
    Link to this post
    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.
  6. Dan Sorensen
    Dan Sorensen avatar
    256 posts
    Registered:
    13 Aug 2010
    27 Sep 2012
    Link to this post
    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?
  7. Arno
    Arno avatar
    249 posts
    Registered:
    08 Sep 2010
    13 Jan 2013
    Link to this post

    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.

  8. Lupi
    Lupi avatar
    38 posts
    Registered:
    17 May 2004
    20 Jan 2013
    Link to this post
    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.
  9. Lupi
    Lupi avatar
    38 posts
    Registered:
    17 May 2004
    20 Jan 2013
    Link to this post
    *Duplicate post*
  10. Lupi
    Lupi avatar
    38 posts
    Registered:
    17 May 2004
    22 Jan 2013
    Link to this post
    *Duplicate post*
  11. Markus
    Markus avatar
    2763 posts
    Registered:
    25 Nov 2005
    11 Mar 2014
    Link to this post

    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

  12. Svetoslav Manchev
    Svetoslav Manchev avatar
    735 posts
    Registered:
    07 Dec 2016
    14 Mar 2014
    Link to this post
    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
     
  13. Arno
    Arno avatar
    249 posts
    Registered:
    08 Sep 2010
    14 Mar 2014
    Link to this post

    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!

  14. Markus
    Markus avatar
    2763 posts
    Registered:
    25 Nov 2005
    14 Mar 2014 in reply to Arno
    Link to this post

    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

  15. MB
    MB avatar
    302 posts
    Registered:
    09 Jan 2005
    14 Mar 2014 in reply to Markus
    Link to this post

    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!

  16. Arno
    Arno avatar
    249 posts
    Registered:
    08 Sep 2010
    15 Mar 2014 in reply to MB
    Link to this post

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

  17. MB
    MB avatar
    302 posts
    Registered:
    09 Jan 2005
    15 Mar 2014 in reply to Arno
    Link to this post

    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.

  18. Markus
    Markus avatar
    2763 posts
    Registered:
    25 Nov 2005
    17 Mar 2014 in reply to MB
    Link to this post

    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

18 posts, 0 answered