SpyriadisNetworkBlob

Umbraco Clear Old Document Versions To Decrease Database Size And Improve Performance

Written by

spairous

in

,

Recently I had a problem with an Umbraco 4.7.1 installation that had became 10GB in SQL Server and had about 20000 published pages. Since there is no way to do something like this from the umbraco administration panel I had to create a T-SQL to clear the database.

DECLARE @createdDate Datetime = '2012-03-01'

DELETE FROM cmsPropertyData WHERE
	versionId NOT IN (SELECT versionId FROM cmsDocument WHERE updateDate > @createdDate OR published = 1 OR newest = 1) AND
	contentNodeId IN (SELECT DISTINCT nodeID FROM cmsDocument)
DELETE FROM cmsPreviewXml WHERE
	versionId NOT IN (SELECT versionId FROM cmsDocument WHERE updateDate > @createdDate OR published = 1 OR newest = 1) AND
	nodeId IN (SELECT DISTINCT nodeID FROM cmsDocument)
DELETE FROM cmsContentVersion WHERE
	versionId NOT IN (SELECT versionId FROM cmsDocument WHERE updateDate > @createdDate OR published = 1 OR newest = 1) AND
	ContentId  IN (SELECT DISTINCT nodeID FROM cmsDocument)
DELETE FROM cmsDocument WHERE
	versionId NOT IN (SELECT versionId FROM cmsDocument WHERE updateDate > @createdDate OR published = 1 OR newest = 1) AND
	nodeId IN (SELECT DISTINCT nodeID FROM cmsDocument)

Warning if you do not have any TSQL experience be careful because you could easily delete all the data in your database. Always take a backup before making any changes this way.

Comments

6 responses to “Umbraco Clear Old Document Versions To Decrease Database Size And Improve Performance”

  1. Matt Brailsford Avatar
    Matt Brailsford

    Hey,

    Nice work on figure this out. Thought you might like to know about this package thought, UnVersion (http://our.umbraco.org/projects/website-utilities/unversion). It lets you set the maximum number of versions of a document to keep, so the system will perform automatic cleanup over time, making it less of a manual process.

    Keep up the good work.

    Matt

    Reply
    1. spyriadi Avatar
      spyriadi

      Nice… I’ll definitely install this in couple of my projects!

      Reply
    2. pawel Avatar
      pawel

      Hello Matt,

      I installed your package, what does “automatic cleanup over time” mean? Will it start working straight after installation?

      regards

      Reply
      1. j Avatar
        j

        pawel: if you look at the source, you’ll see that this code is tied to the “publish” trigger and checks for the DocType of the published document.

        So, if you had a doctype of “news” – all “news” documents would get their versions cleaned up when the next “news” document was published. But any other doctypes wouldn’t be touched at that point.

        There is also an “$_ALL” type that seems to run whenever any type is published.

        Reply
  2. alexsts Avatar
    alexsts

    Too bad UnVersion package is not compatible U6.X.X.
    Is anyopne knows how to implement this into U6?

    Reply
    1. David Dimmer Avatar
      David Dimmer

      We have some SQL scripts to fix this, if you need help let us know we are located just north of Chicago and have multiple certified Umbraco developers on staff at Fyin.com.
      -David
      LinkedIn

      Reply

Leave a Reply to j Cancel reply

Your email address will not be published. Required fields are marked *

More posts