Hi Christo

What version of Umbraco are you using?

The problem is that Umbraco stores previous versions of content items, so with time database grows.

Thanks,

Alex

Try to use this sql script for clearing database, and don't forget about backup -

-- Umbraco Clear Old Document Versions To Decrease Database Size And Improve Performance
-- http://borism.net/2008/12/16/fixing-a-large-cmspropertydata-table-in-umbraco/
DECLARE @createdDate Datetime = DATEADD(m, -1, getdate())

-- dump logs
-- TRUNCATE TABLE umbracolog -- faster if log table is very big and you don't need anything
DELETE FROM umbracolog WHERE Datestamp < @createdDate

-- clean up old versions
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)

--http://blog.dampee.be/post/2015/11/28/Remove-Old-versions-of-Umbraco-Previews.aspx
delete
--select *
from cmsPreviewXml
where versionId in (
    select cmsPreviewXml.versionId
    from cmsPreviewXml join cmsDocument on cmsPreviewXml.versionId=cmsDocument.versionId
where cmsDocument.newest <> 1)

Good day Alex

I have tried this script and the problem is I cannot delete a billion records as the hard drive run out of space before even a 100000 has been deleted(Log file issue, growing log file), I would use this script as soon as the first clean up has been done. I am using Umbraco 6.2.2.

Thanks for the reply

Without much thought on the matter (on my front), have you tried changing the MS-SQL recovery model?

Details from Microsoft are here: https://technet.microsoft.com/en-us/library/ms190203(v=sql.105).aspx

You'll need the BULKLOGGED mode for this operation. Switch to it, purge the transaction log to empty and then start deleting data.

With a backup of course.

Thinking of a backup, if you don't have space on the live system an alternative would be to perform a full DB backup, transfer the backup files to a less constrained system, restore the DB, perform the maintenance, backup the smaller database and then, here's the slightly scary bit - delete the database entirely (check all files are gone as well) and restore the smaller database in its place.

I've used this script in the past to remove extra content versions. It loops through and uses transactions, which prevents the log file getting too large. As with any script as a disclaimer, please backup the database first.

/* This script will delete extraneous content versions */
delete from cmsPreviewXml where VersionId in (select versionId from cmsDocument where newest = 0)
delete from cmsContentVersion where VersionId in (select versionId from cmsDocument where newest = 0)
delete from cmsDocument where newest = 0



DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;

WHILE (@Deleted_Rows > 0)
  BEGIN

   BEGIN TRANSACTION

     delete top(1000) from cmsPropertyData where VersionId in (select versionId from cmsDocument where newest = 0)

   SET @Deleted_Rows = @@ROWCOUNT;

   COMMIT TRANSACTION
   CHECKPOINT -- for simple recovery model
END

is working on a reply...