Umbraco 7 optimise (6 months records)

umbracooptimise.sql

-- 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, -6, getdate())
print '=================================================='
print 'CreatedDate: ' + CAST(@createdDate  AS VARCHAR)
print '=================================================='
print ''
print ''
print ''
GO


DECLARE @createdDate Datetime = DATEADD(m, -6, getdate())
print '=================================================='
print 'UmbracoLog'
print '=================================================='
DELETE FROM umbracolog WHERE Datestamp < @createdDate
print '=================================================='
print '/UmbracoLog'
print '=================================================='
print ''
print ''
print ''
GO


-- clean up old versions
DECLARE @createdDate Datetime = DATEADD(m, -6, getdate())
print '=================================================='
print 'cmsPropertyData'
print '=================================================='
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)
print '=================================================='
print '/cmsPropertyData'
print '=================================================='
print ''
print ''
print ''
GO


DECLARE @createdDate Datetime = DATEADD(m, -6, getdate())
print '=================================================='
print 'cmsPreviewXml'
print '=================================================='
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)
print '=================================================='
print '/cmsPreviewXml'
print '=================================================='
print ''
print ''
print ''
GO


DECLARE @createdDate Datetime = DATEADD(m, -6, getdate())
print '=================================================='
print 'cmsContentVersion'
print '=================================================='
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)	
print '=================================================='
print '/cmsContentVersion'
print '=================================================='
print ''
print ''
print ''
GO


DECLARE @createdDate Datetime = DATEADD(m, -6, getdate())
print '=================================================='
print 'cmsDocument'
print '=================================================='
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)
print '=================================================='
print '/cmsDocument'
print '=================================================='
print ''
print ''
print ''
GO


DECLARE @createdDate Datetime = DATEADD(m, -6, getdate())
print '=================================================='
print 'cmsPreviewXml2'
print '=================================================='
--http://blog.dampee.be/post/2015/11/28/Remove-Old-versions-of-Umbraco-Previews.aspx
delete from cmsPreviewXml where 
	versionId in (select cmsPreviewXml.versionId from cmsPreviewXml 
	join cmsDocument on cmsPreviewXml.versionId=cmsDocument.versionId where cmsDocument.newest <> 1)
print '=================================================='
print '/cmsPreviewXml2'
print '=================================================='
GO