Sign in Register
Go to solution
Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • David Armitage 510 posts 2081 karma points
    Mar 03, 2017 @ 03:35
    David Armitage
    0

    Umbraco Database Size Issue (2.5GB++) - URGANT (PLEASE HELP)

    Hi Guys,

    I have about 20 websites build with Umbraco and I have been monitoring the database sizes over a period of time.

    Some of the database sizes are exponentially getting bigger. I have experienced this problem in the past and got some useful information for the following URL.

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

    I get that this problem may be due to content versions but I just dont know how I can fix this.

    1. Why does Umbraco do this? I get that they want to be able to roll back but I have never once had to roll back and I defiantly don't want to be able to roll back to day once. I don't get why this is not a configurable setting (or is it?).

    2. I tested the deleting older versions of the content by using the SQL provided in the above url. This in facet increased my database size my about 5 to 6 times the size. It did throw and error. I don't think it could delete the older versions.

    Error Details: Msg 547, Level 16, State 0, Line 32 The DELETE statement conflicted with the REFERENCE constraint "FKcmsPreviewXmlcmsContentVersionVersionId". The conflict occurred in database "hcmaustraliacom_au", table "dbo.cmsPreviewXml", column 'versionId'.

    1. I tested using the FALM Housekeeping package which is meant to do this. Again this increase my database size by about 5 to 6 times. I am guessing this package is experiencing the same issues as the raw SQL query I tried.

    2. I know there is also the UnVersion package. I have used this in the past but haven't tested it yet on this website. I will later but I want more of a visual fix (possibly SQL query) and to understand the issue better.

      • To give you an idea of the database sizes some are exceeding 2.5 GB for a simple website. There are quite a few like this. Others sit at around 500MB to 1GB. Crazy sizes for such simple websites.

      • The umbraco version for this particular website is Umbraco version 7.4.3 assembly: 1.0.5948.18141

      • Ideally I would like some SQL that can clear these versions then would like to run the versioning off. I could possibly install UnVersion at this stage.

    Thanks so much in advanced for any help.

    Kind Regards

    David

    Copy Link
  • Ismail Mayat 4511 posts 10092 karma points MVP 2x admin c-trib
    Mar 03, 2017 @ 09:08
    Ismail Mayat
    100

    David,

    I use the following script which does shrink dbs for us

    truncate table umbracoLog

    -- truncate elmah table if it exist IF OBJECTID('ELMAHError') IS NOT NULL TRUNCATE TABLE ELMAH_Error

    -- Delete all recycle bin nodes DELETE FROM cmsPreviewXml WHERE nodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM cmsContentVersion WHERE contentId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM cmsDocument WHERE nodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM cmsContentXML WHERE nodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM cmsContent WHERE nodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM cmsPropertyData WHERE contentNodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20)

    -- delete the XML nodes... DELETE FROM umbracoDomains WHERE domainRootStructureID IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM umbracoUser2NodePermission WHERE nodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM umbracoRelation WHERE parentId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM umbracoRelation WHERE childId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM cmsTask WHERE nodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM cmsTagRelationship WHERE nodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20

    -- Empty the Media Recycle Bin. This is not extensively tested! -- Use at your own risk! Love, Thom delete from dbo.cmsContentVersion where contentid in (select Id from dbo.umbracoNode where path like '%-21%' and Id !=-21) delete from dbo.cmsContentXml where nodeId in (select Id from dbo.umbracoNode where path like '%-21%' and Id !=-21) delete from dbo.cmsContent where nodeId in (select Id from dbo.umbracoNode where path like '%-21%' and Id !=-21) delete from dbo.cmsPropertyData where contentNodeId in (select Id from dbo.umbracoNode where path like '%-21%' and Id !=-21)

    -- delete property data -- date format... yyyy-mm-dd DECLARE @createdDate Datetime = getDate()

    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)

    -- Reindex tables
    DBCC DBREINDEX (cmsPropertyData) DBCC DBREINDEX (cmsPreviewXml) DBCC DBREINDEX (cmsContentVersion) DBCC DBREINDEX (cmsDocument) DBCC DBREINDEX (cmsContentXml) DBCC DBREINDEX (umbracoDomains) DBCC DBREINDEX (umbracoUser2NodePermission) DBCC DBREINDEX (umbracoNode) DBCC DBREINDEX (cmsContent)

    -- Shrinkage! ALTER DATABASE CURRENT SET RECOVERY SIMPLE;

    DECLARE @logFile nvarchar(200) = (select Name from sys.databasefiles where Name like '%log') select @logFile

    DBCC SHRINKFILE(@logFile)

    DECLARE @dataFile nvarchar(200) = (select Name from sys.databasefiles where Name NOT like '%log') select @dataFile DBCC SHRINKFILE(@dataFile)

    Please note backup db before using just in case!

    Also test before using!

    Copy Link
  • David Armitage 510 posts 2081 karma points
    Mar 03, 2017 @ 09:19
    David Armitage
    0

    Hi,

    I already have the database file set as simple so I am guessing the SQL I need to run is just this part?

    DECLARE @createdDate Datetime = getDate()

DELETE FROM cmsPreviewXml WHERE nodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM cmsContentVersion WHERE contentId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM cmsDocument WHERE nodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM cmsContentXML WHERE nodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM cmsContent WHERE nodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM cmsPropertyData WHERE contentNodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20)

DELETE FROM umbracoDomains WHERE domainRootStructureID IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM umbracoUser2NodePermission WHERE nodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM umbracoRelation WHERE parentId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM umbracoRelation WHERE childId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM cmsTask WHERE nodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM cmsTagRelationship WHERE nodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20

DELETE FROM dbo.cmsContentVersion where contentid in (select Id from dbo.umbracoNode where path like '%-21%' and Id !=-21) delete from dbo.cmsContentXml where nodeId in (select Id from dbo.umbracoNode where path like '%-21%' and Id !=-21) delete from dbo.cmsContent where nodeId in (select Id from dbo.umbracoNode where path like '%-21%' and Id !=-21) delete from dbo.cmsPropertyData where contentNodeId in (select Id from dbo.umbracoNode where path like '%-21%' and Id !=-21)

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)

    Let me know and I will give it a go...

    Kind Regards

    David

    Copy Link
  • Ismail Mayat 4511 posts 10092 karma points MVP 2x admin c-trib
    Mar 03, 2017 @ 09:27
    Ismail Mayat
    0

    David,

    Possibly. The main bulk of junk will bein cmspreviewxml cmscontentversion and cmspropertydata. Clearing out those tables where you have deleted nodes should shrink up the db.

    Just make sure you backup first just in case ;-}

    Copy Link
  • David Armitage 510 posts 2081 karma points
    Mar 03, 2017 @ 10:53
    David Armitage
    0

    Hi Mate,

    Awesome thanks. I can confirm I can fix this up by....

    *Running the following code as you provided.

    DECLARE @createdDate Datetime = getDate()

DELETE FROM cmsPreviewXml WHERE nodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM cmsContentVersion WHERE contentId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM cmsDocument WHERE nodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM cmsContentXML WHERE nodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM cmsContent WHERE nodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM cmsPropertyData WHERE contentNodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20)

DELETE FROM umbracoDomains WHERE domainRootStructureID IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM umbracoUser2NodePermission WHERE nodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM umbracoRelation WHERE parentId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM umbracoRelation WHERE childId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM cmsTask WHERE nodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM cmsTagRelationship WHERE nodeId IN (SELECT id FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20) DELETE FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20

DELETE FROM dbo.cmsContentVersion where contentid in (select Id from dbo.umbracoNode where path like '%-21%' and Id !=-21) delete from dbo.cmsContentXml where nodeId in (select Id from dbo.umbracoNode where path like '%-21%' and Id !=-21) delete from dbo.cmsContent where nodeId in (select Id from dbo.umbracoNode where path like '%-21%' and Id !=-21) delete from dbo.cmsPropertyData where contentNodeId in (select Id from dbo.umbracoNode where path like '%-21%' and Id !=-21)

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)

DBCC DBREINDEX (cmsPropertyData) DBCC DBREINDEX (cmsPreviewXml) DBCC DBREINDEX (cmsContentVersion) DBCC DBREINDEX (cmsDocument) DBCC DBREINDEX (cmsContentXml) DBCC DBREINDEX (umbracoDomains) DBCC DBREINDEX (umbracoUser2NodePermission) DBCC DBREINDEX (umbracoNode) DBCC DBREINDEX (cmsContent)

    *Then using Management Studio to right click on the database then go into tools and manually shrink the database.

    *I assume these lines of SQL are supposed to auto shrink the database file rather than me having to do this manually?

    ALTER DATABASE CURRENT SET RECOVERY SIMPLE;

DECLARE @logFile nvarchar(200) = (select Name from sys.databasefiles where Name like '%log') select @logFile

DBCC SHRINKFILE(@logFile)

DECLARE @dataFile nvarchar(200) = (select Name from sys.databasefiles where Name NOT like '%log') select @dataFile DBCC SHRINKFILE(@dataFile)

    *I attempted running these lines of SQL but get an error Invalid object name 'sys.databasefiles'.

    Is there some part of this SQL I should be replacing (database name possibly?). I am Ok with SQL but not much experiencing with database management. It would be awesome if I could get these lines of SQL working to save me manually having to do this.

    Thanks Again.

    David

    Copy Link
  • Damiaan 442 posts 1302 karma points MVP 6x c-trib
    Mar 13, 2017 @ 16:28
    Damiaan
    0

    It's already solved, but I have a gist for that.

    Copy Link
  • Amir Khan 1289 posts 2746 karma points
    Mar 13, 2017 @ 16:44
    Amir Khan
    1

    Here's a useful package for managing versioning as well: https://our.umbraco.org/projects/website-utilities/unversion/

    Especially useful during initial development where you don't want to save every version.

    Copy Link
Please Sign in or register to post replies

Write your reply to:

Draft