Instantly share code, notes, and snippets.

Embed
What would you like to do?
Umbraco Database cleanup. After pulling in an umbraco database from production, you don't need all history or log.
-- 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)
@jahanalem

This comment has been minimized.

jahanalem commented Sep 13, 2017

Although I executed the query but still the db is too big!
Is there another solution?
executedq

https://our.umbraco.org/forum/core/general/25636-Database-size-problem#comment-278669

@dampee

This comment has been minimized.

Owner

dampee commented Aug 15, 2018

That is the log file. The real data is only 9MB.

You need to backup your database so the transactionlog can be cleared. Some people switch to "simple" instead of a full transactionlog. You could do that for dev environments.

@dstream

This comment has been minimized.

dstream commented Sep 4, 2018

Should we excute SHRINKDATABASE and SHRINKFILE after that?

DBCC SHRINKDATABASE (N'DatabaseName')
DBCC SHRINKFILE (N'DatabaseName' , 0, TRUNCATEONLY)"

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment