Copied to clipboard

Flag this post as spam?

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


  • Kim Larsen 29 posts 72 karma points
    Sep 22, 2011 @ 12:25
    Kim Larsen
    0

    Minimize MSSQL database

    I have a very small site developed using Umbraco - http://www.blomsterdekoratoer.dk/ - but even if the site is very small and is not being updated the database is very large and seems to be growing. At the moment the size of the database is ~80MB and I have a limit of 100MB togehter with other databases so that is a problem. I have developed other sites not using Umbraco and even if they contain a lot more content the databases here are only 3-5MB.

    I have tried using Management Studio to compact the database, but that only gave me ~5MB.

    Is there som kind of tool I can use to minimaze what must be Umbraco-specific "nonsense" - I dont need history etc of site content - or is only option to change to SQL CE as HD space is not a problem?

  • Christian Stenfors 80 posts 124 karma points
    Nov 04, 2011 @ 19:23
    Christian Stenfors
    0

    i have the same exact problem...

  • Kim Larsen 29 posts 72 karma points
    Nov 05, 2011 @ 12:38
    Kim Larsen
    0

    I found an Umbraco Project that made it possible to see and delete logs (it could also be done directly through Management Studio) - and logs was what was killing my database. The reasen to the log-overhead was a mis-configuration on the server - missing rights.

  • Sherry Ann Hernandez 320 posts 344 karma points
    Nov 09, 2011 @ 11:17
    Sherry Ann Hernandez
    0

    Hi Kim,

    Can you share how you manage to trim down the umbraco database?

  • David Verberckmoes 46 posts 77 karma points
    Nov 09, 2011 @ 11:23
    David Verberckmoes
    0

    Hi,

    you can use the Log Manager package to view and delete logs: http://our.umbraco.org/projects/developer-tools/log-manager

    Rgds,

    David

  • Kim Larsen 29 posts 72 karma points
    Nov 10, 2011 @ 10:34
    Kim Larsen
    0

    Exactly - Use the Log Manager or install Management Studio from SQL Server.

  • Sherry Ann Hernandez 320 posts 344 karma points
    Nov 10, 2011 @ 16:16
    Sherry Ann Hernandez
    0

    I deleted the logs and the old version of our sites but still our database is around 110mb. This is for 4000 pages.

  • Kim Larsen 29 posts 72 karma points
    Nov 10, 2011 @ 16:27
    Kim Larsen
    0

    Then I think you need to either analyse through Management Studio which table that use the space or maybe if it is the transaction log - if it is the transaction log you can shrink the database using Management Studio.

  • Martin Lingstuyl 202 posts 379 karma points
    Feb 15, 2012 @ 15:14
    Martin Lingstuyl
    0

    I had the same problem of an oversized Log table in umbraco 4.5.2 (this was due to a problem with the Examine Indexer)
    In some strange way though, also my previewXml, ContentVersion and PropertyData tables were very large (700mb, 800mb)

    After some checking out, it seemed like Umbraco had created hundreds of versions for each page, if you've got a lot of pages, this of course results in an enormous dbase overhead.

    What I did was the following

    1) Truncate the previewXml table. This is only used for previewing when editing.

    2) Delete all versions in the version table except the last for each node

    DELETE FROM cmsContentVersion
    WHERE id NOT IN
    ( SELECT MAX(id) FROM cmsContentVersion GROUP BY ContentId )

    3) Delete all Propertydata records that corresponded to just deleted versions

    DELETE FROM cmsPropertyData
    Where versionId NOT IN (SELECT VersionId FROM cmsContentVersion
    WHERE ContentId = cmsPropertyData.contentNodeId)

     

    At least now the dbase is normal size again. (from 2.8 gig to 289 mb)

     

  • AmandaEly 123 posts 379 karma points
    Aug 31, 2015 @ 07:31
    AmandaEly
    0

    Well, I liked the look of those queries but I gave up after 35 minutes on the first one. It is huge! I am considering using a cursor to go through each row separately, comparing against the selection.

    But to be honest, I would so much rather Umbraco dealt with this problem for me. I don't want all that version info stored for each property anyway.

  • AmandaEly 123 posts 379 karma points
    Aug 31, 2015 @ 11:58
    AmandaEly
    0

    I'm going to have a go with UnVersions, which is compatible with 7.2

Please Sign in or register to post replies

Write your reply to:

Draft