How to Delete Old and Unpublished Content in Umbraco to save Database Space

Also have a look our article for removing log entries and shrinking your DB

First thing - make sure you have a backup of your DB, do not run this code unless you are sure what you are doing! It will delete content from your Umbraco database.

By default Umbraco keeps old versions of your content, so you DB can get quite large if you are making lot of changes.

If you need to save space in your Umbraco DB, you can delete the old versions and delete any unpublished content.

You will need to do this via Microsoft SQL Server Management Studio (SSMS), which if you do not have installed can be found here. You will also need to open up the firewall to allow you to connect to the SQL Server, this is shown here.

Follow these steps to remove your old content...

1. Open Microsoft SQL Server Management Studio and connect to your database. Details of the IP address and username/password can be found in your 'Space Summary'.
2. Click on the 'New Query' button. Using F5 or the 'Execute' button to run each script.
3. You can view your table sizes within your Umbraco database, to determine if the umbracoLog table is the reason for your database size, by using the following script snippet:

   DELETE FROM cmsPropertyData WHERE
       versionId NOT IN (SELECT versionId FROM cmsDocument WHERE 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 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 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 published = 1 OR newest = 1) AND
       nodeId IN (SELECT DISTINCT nodeID FROM cmsDocument)

That's it - you have now removed old content from the DB - if you are running Umbraco 7, you may also need to clear it's cache - you can do this by going to the URL on your site..

http://yoursite.com/Umbraco/dialogs/republish.aspx?xml=true

Return to Category