Thanks Dirk, it was helpful for clearing logs but as Thomas said it timed out on histories.

it showed
104 Content
58955 Versions
27856 documents
186233 property data

Just upgraded to 3.0.5 and have been running the client tools deleting logs when I can. My main problem at this point was the troubled document and that I couldn''t delete it. (At first a folder and a document, but finally I got rid of the document through the ui... well, partially). As they say in the other threads it keeps trying to be published several times per minute, which I think is quicker than the delete action works (or maybe it's that the folder had copied itself 27764 times).

I've been working with the client tools, cleaned up my logs and removed a lot of old versions, but will need to manually get rid of this document in the db. Shouldn't be too tough right?

So after a fresh backup I ran

DELETE FROM cmsDocument WHERE nodeID = 1217

27764 rows affected

Everything seems to be in stable now, yet there's a giant (270mb) cmsPropertyData table. I can successfully run the Client Tools, but they do not remove the histories of 1217 or 1218 (2 problems files that must have been corrupted during a copy).

104 Content
58955 Versions
92 Documents
186233 Property Data

So now I believe I need to clear cmsPropertyData of the 185714 items with contentNodeID 1218.

Fresh Backup, then

DELETE FROM cmsPropertyData WHERE contentNodeID = 1218

185714 rows affected

Clicking around it still looks good. Nothing obviously out of whack.

Finally:

DELETE FROM cmsContentVersion WHERE contentID in (1218,1217)

58807 row(s) affected

104 Content
153 Versions
97 documents
532 property data

Everything still seems ok on the site and in umbraco. Umbraco runs much faster now.

All of those files were from the past 48 hours of the system constantly copying and republishing 2 broken documents.

Interesting Bug. I'll let everyone know if something went wrong in all of this.

Also, does anyone know of other tables that I should clean out so as not to leave residual junk around?

WARNING:

If this is a SQL database, executing a DELETE statement over that many records is going to seriously expand your database log and take a very very long time to execute. It will likely also lock the entire table and stop you from publishing or access the database.

If you are going to go ahead with this strategy, I would highly recommend that you ensure that your disk drives have about double the amount of space free that you intend to delete e.g. 11GB just for the database log. You may not need it all, but this operation is going to take a looooooong time and you don't want it all to be for nothing and roll-back again just because the log runs out of space.

Usually when I have the situation where I am throwing away more data than I am keeping AND I have to keep the system online as much as possible, I follow a recipe like the following:

- script the table that you are deleting from and create a new one (with a different name e.g. cmsPropertyData_NEW)

- start a transaction

- copy the data that you are keeping to the new table

- rename the old table to any other name e.g. cmsPropertyData_OLD

- rename the new table to the original table name (e.g. cmsPropertyData_NEW to cmsPropertyData)

- commit the transaction

- truncate the old table

- drop the old table

This procedure should be quicker and use less database log space.

Your other option is to delete chunks of rows a few at a time instead of just running DELETE against all the rows that your want to remove at once. This way the database will still be available to access between chunks.

---

Neil