That did the trick, - thank you! However, a closer look reveals that the inactive regions are only a small part of the problem :-( Only about 2Mb of the 600Mb total!
500Mb is devoted to assets - 9500 of them! Over half textures and a great many duplicates (to judge from the descriptions anyway). I don't see any easy fix by inspection, since the assets depend on other tables to identify if they are used. Karen --- On Wed, 12/23/09, Michael <[email protected]> wrote: > From: Michael <[email protected]> > Subject: Re: [Opensim-users] Database cleanup > To: [email protected] > Date: Wednesday, December 23, 2009, 12:21 AM > Inactive regions are pretty easy to fix. > But, as always, a backup is recommended before poking that > thar bear with a stick. :) > Ya just need to take your favorite database > tool, and connect to your OpenSim db. First you'll need > the UUIDs of any inactive regions. If you no longer have a > record of this somewhere (say, in an old XML or INI region > file) you can compare the UUIDs from your active regions to > the results of the following query, and take note of the > ones that are dead: > > SELECT RegionID FROM estate_map; > Looks to me like there's only really two > tables to worry about here named "prims" and > "primshapes". We'll start with safe queries > that don't actually change anything, so you can get a > feel for whether this is worthwhile for you. We'll also > only do one stale region for the sake of simplicity. > Let's start by seeing just how many rows you might be > able to remove. Run this simple query: > > SELECT COUNT(*) FROM prims WHERE RegionUUID = > '<OldRegionUUID>'; > Replace <OldRegionUUID> with the inactive > region's UUID. That query will return a number > equaling the number of stale prims sitting in that > table. > > That's the prims table, which is easiest > since each prim has a RegionUUID column. The primshapes > table doesn't, so we'll have to use the prims table > to figure out which shapes are stale. The following query > will do this for you: > > SELECT COUNT(*) FROM > primshapes WHERE EXISTS > (SELECT * FROM prims WHERE > prims.RegionUUID = > '<OldRegionUUID>' AND > prims.UUID = primshapes.UUID); > > This *should* return the exact same number for > primshapes that that you got from the query to the prims > table. If you do not, stop right here, and don't make > any changes to your database. If both counts are the same > though, you now know how many rows you can remove by > combining the two counts. So now we'll remove those > rows. Make sure you have that backup before the next > steps. > > Now we'll remove the stale prims from the > primshapes table. It's important to do this with > primshapes before modifying the prims table. The following > query will do so: > > DELETE FROM primshapes WHERE > EXISTS (SELECT * FROM prims > WHERE prims.RegionUUID = > '<OldRegionUUID>' AND > prims.UUID = primshapes.UUID); > > Finally, the query to remove them from the prims > table: > DELETE FROM prims WHERE RegionUUID = > '<OldRegionUUID>'; > And there you have it. These queries removed > over 9000 rows from my little OpenSim database. You can also > remove those stale region UUIDs from the estate_* tables, > but that should be pretty easy to do via a GUI of your > choice since these tables tend to be very small in > comparison to the prim tables. I just left them out of sheer > laziness... they're a single row, so the space they take > up is insignificant. > > Marcus Llewellyn > > On Tue, Dec 22, 2009 at 6:27 PM, > Karen Palen <[email protected]> > wrote: > > I > definitely have the remove/replace region problem. I suspect > that I have more than that too though :-) > > > > Karen > > > > -----Inline Attachment Follows----- > > _______________________________________________ > Opensim-users mailing list > [email protected] > https://lists.berlios.de/mailman/listinfo/opensim-users > _______________________________________________ Opensim-users mailing list [email protected] https://lists.berlios.de/mailman/listinfo/opensim-users
