|
|
Rank: Enthusiast
Joined: 5/22/2008
Posts: 33
Location: Binghamton, NY, US
|
Not sure what's going on, but my database has grown to almost 400 MB on a relatively small site... The cmsPropertyData table is 392+ MB of that.
Seems excessive. Any ideas what's going on?
|
|
Rank: Addict
Joined: 7/19/2006
Posts: 881
Location: Århus, Denmark
|
I have sometimes seen extreme growth on sites where a node somehow is publishing itself in a loop every minute or more. This creates a new version every time, which will cause the DB to grow rapidly. Try to see if you can identify a nodeID in the cmsPropertyData table that is saving a lot of versions. Then find the node in umbraco, copy it, and delete the original. That should stop it from republishing itself (If that is indeed the problem in your case)
|
|
Rank: Umbracoholic
Joined: 9/8/2006
Posts: 2,020
Location: MA, USA; Cambridge, UK
|
You can also trim out old versions with Thomas Hoelher's excellent ClientTools4Umbraco ( http://www.codeplex.com/ClientTools4Umbraco). Be sure to backup your database before doing anything... just in case :) cheers, doug.
MVP 2007-2009 - Percipient Studios - Percipient Blog
|
|
Rank: Devotee
Joined: 3/19/2008
Posts: 69
Location: London
|
And if you have access to SQL Server Management Studio (2005) or SQL Enterprise Manager (2000), check the properties of the UmbracoLog table and see how many rows you have or how big the table data is in MB?
Neil
|
|
Rank: Enthusiast
Joined: 5/22/2008
Posts: 33
Location: Binghamton, NY, US
|
neilf wrote:And if you have access to SQL Server Management Studio (2005) or SQL Enterprise Manager (2000), check the properties of the UmbracoLog table and see how many rows you have or how big the table data is in MB? Neil, checked the log table, but it's okay. It's really the properties table that's so big. Thanks for all the help, guys. I'll try the suggestions. UMBRACO RULES! ;-)
|
|
Rank: Umbracoholic
Joined: 3/17/2008
Posts: 1,192
Location: Nyborg, Denmark
|
Sounds like a known (and fixed) bug caused by a corrupted document with a release/expire date. This issue is fixed in 3.0.5 and 4.x, so also upgrading should help the db from growing more. A cleanup is still recommended as the large amount of data is likely to affect performance in editing mode.
Jeeeez, did I really start this :-)
Founder of the Umbraco project
|
|
Rank: Enthusiast
Joined: 5/22/2008
Posts: 33
Location: Binghamton, NY, US
|
hartvig wrote:Sounds like a known (and fixed) bug caused by a corrupted document with a release/expire date. This issue is fixed in 3.0.5 and 4.x, so also upgrading should help the db from growing more. A cleanup is still recommended as the large amount of data is likely to affect performance in editing mode. What is the recommended "clean up" procedure? My cmsPropertyData table has 232330 rows and is consuming 392536KB (392.5 MB) of space (368968 KB data, 19320 KB index, 4248 KB unused space). With so many rows, I'm not sure how to hunt down the offending node(s).
|
|
Rank: Umbracoholic
Joined: 3/17/2008
Posts: 1,192
Location: Nyborg, Denmark
|
1) Upgrade to the latest version!
2) Try finding the node(s) that's causing the problem, most likely the ones with most votes. It should be possible with the following query (haven't tested it though): select contentNodeId, count(contentNodeId) from cmsPropertyData group by contentNodeId order by count(contentNodeid) desc
3) If it's just a couple of nodes, then the easiest thing is to do what Morten suggest. Copy the node into the same place and delete the old one (make sure to rename the copied one afterwards so it's not called "xxxxx (1)"). If there's a lot try using THöhlers brilliant tool.
Hope this helps!
/n
Jeeeez, did I really start this :-)
Founder of the Umbraco project
|
|
Rank: Enthusiast
Joined: 5/22/2008
Posts: 33
Location: Binghamton, NY, US
|
Ok, got it! Found a node that had 222,000+ rows. I deleted all but the most recent version and the cmsPropertyData table is now down to 40MB. Whew!
Thanks guys.
And again.. UMBRACO RULES! ;-)
|
|
Rank: Addict
Joined: 7/19/2006
Posts: 634
Location: Bad Homburg, Germany
|
Two weaks ago a friend had this problem but with a 8 GB database, so we had lost documents with also lost propertydatas in the database. I made a sql script which cleaned this. Also I will upgrade the clienttools regarding this scenario. So I can reply the sql script this evening (it is at home) or you can mail me via th at thoehler dot com Chers, Thomas
• 2007/2008 MVP • www.thoehler.com • blog.thoehler.com • Bad Homburg, Germany
|
|
Rank: Addict
Joined: 7/19/2006
Posts: 634
Location: Bad Homburg, Germany
|
You are all too fast for me...  Thomas
• 2007/2008 MVP • www.thoehler.com • blog.thoehler.com • Bad Homburg, Germany
|
|
Rank: Administration
Joined: 7/25/2006
Posts: 448
Location: vipperoed, denmark
|
hoehler wrote:Two weaks ago a friend had this problem but with a 8 GB database, so we had lost documents with also lost propertydatas in the database. I made a sql script which cleaned this. Also I will upgrade the clienttools regarding this scenario. So I can reply the sql script this evening (it is at home) or you can mail me via th at thoehler dot com
Chers,
Thomas .. that was me :-) Thanks to Thomas the db was reduced from 8 GB to 320 MB ... and there's been no problems with the site since then. thanks, Jesper
webbureau jesper.com doing webdesign / development / umbraco implementations / 2007&2008 MVP / umbraco certified
|
|
Rank: Devotee
Joined: 7/20/2006
Posts: 55
Location: Copenhagen, Denmark
|
Thomas, Can you please post the cleanup sql here, as I have the same issue too ? - or send it to me by mail to martin at bentzen dot dk Regards and Thanks Martin
My tweets on Twitter
|
|
Rank: Fanatic
Joined: 11/16/2007
Posts: 264
Location: Surrey, UK
|
hartvig wrote:Sounds like a known (and fixed) bug caused by a corrupted document with a release/expire date. This issue is fixed in 3.0.5 and 4.x, so also upgrading should help the db from growing more. Interestingly, I had this problem in a 3.0.5 site just recently!? (Assembly version: 1.0.3050.25839) I noticed a massive amount of "publish" log entries, happening every minute. I found the offending node / document and deleted / recreated it - it's been fine ever since.
Gordon Saxby | Ascend Internet Limited | Web Site Development. Web Site Hosting (inc Umbraco)
|
|
Rank: Fanatic
Joined: 6/25/2007
Posts: 336
Location: Malmo, Sweden
|
hoehler wrote:I made a sql script which cleaned this. Also I will upgrade the clienttools regarding this scenario. When will this be available in the client tools? We are currently having issues with a db that has grown too much and are very interested in a way to easily correct this through your client tools, we are already using it to clean out the log and earlier versions but as there are so many versions the client tool hangs/crashes after a while. We have had to restart the client tools several times, and its been running off and on for two days now but still have not managed to clear all the older versions yet. Thanks for a great tool anyway! I hadn't used it since it was renamed and I was very surprised with the new and improved functions in v1.5 alpha. Thanks!
Is it me or umbraco? Umbraco Certified Professional Level 2.
|
|
Rank: Addict
Joined: 7/19/2006
Posts: 634
Location: Bad Homburg, Germany
|
I will do my very best to finish the new versions the next days. Therefore that I am not getting any mail from this forum (I am following the forum by rss) please mail me if you have immediatly problems. One thing this time: please check if you have lost documents. In the database from Jesper there had been lost data. You can check this with the following sql: Code:SELECT * FROM umbracoNode
WHERE nodeObjectType = 'c66ba18e-eaf3-4cff-8a22-41b16d66a972'
AND ID not in (select Nodeid from cmsDocument) If you get results you have lost documents. The same for smcContentVersion and cmsPropertyData. hth, Thomas
• 2007/2008 MVP • www.thoehler.com • blog.thoehler.com • Bad Homburg, Germany
|
|
Rank: Fanatic
Joined: 6/25/2007
Posts: 336
Location: Malmo, Sweden
|
Using the SQL you provided we find that we have several lost documents.
It would be great if you could email the clean up script!
Mail it to daniel.lindstrom AT corp.sbbs.se
Many thanks!
Is it me or umbraco? Umbraco Certified Professional Level 2.
|
|
Rank: Enthusiast
Joined: 7/23/2008
Posts: 42
Location: Copenhagen
|
Thomas, I'd also really appreciate if you could send me the cleanup script: rmitache@hotmail.comThanks a lot. Radu
|
|
Rank: Addict
Joined: 7/19/2006
Posts: 634
Location: Bad Homburg, Germany
|
Script sent, I will post it the next day here too Thomas
• 2007/2008 MVP • www.thoehler.com • blog.thoehler.com • Bad Homburg, Germany
|
|
Rank: Fanatic
Joined: 6/25/2007
Posts: 336
Location: Malmo, Sweden
|
hoehler wrote:Script sent, I will post it the next day here too
Thomas I haven't received it. Please post it also to daniel.lindstrom AT corp.sbbs.se Thanks!
Is it me or umbraco? Umbraco Certified Professional Level 2.
|
|
The forum has moved
This forum is no longer in use, so you can't reply to this message - please go to Our Umbraco
|
|
Guest |