On Sun, Mar 14, 2010 at 07:19:59PM -0400, Matthew L. Creech scratched on the wall: > Hi, > > I have a SQLite database with one large table, and I'd like to shrink > the size of that table to free up space in the filesystem. My problem > is that the database is (for example) 100 MB, and I have 80 MB of free > filesystem space. I figured that I could DELETE, say, half of the > records from the table, then VACUUM, and the VACUUM would > [temporarily] need ~50 MB of free space for the journal (since that's > how much real data there is). > > Instead, I'm finding that it needs a full 100 MB for the journal, even > though once the VACUUM succeeds the resulting DB is only 50 MB. As a > result, I'm stuck unable to shrink the database, since VACUUM fails > with a disk I/O error (out of space), seemingly no matter many entries > I remove ahead of time. I know the space is being freed, since > "PRAGMA freelist_count" shows the expected numbers. So presumably > this is just an artifact of the way VACUUM is implemented internally.
Are you sure it is the journal file that is growing too large? VACUUM works by making a logical copy of the database from the original database into a temp database. This restructures the database and recovers space. The temp database is then copied back to the original database using low-level page copy. This low-level copy then truncates the original database file, recovering filesystem space. This also means the total space required to VACUUM a database is: [old database size] + [new database size] + [journal file] While I have not tested this, I was under the impression that the journal file is very very small, as no modifications are made to the database, other than the final low-level copy (which is not a journaled operation). Now, if I'm following you correctly, the numbers you gave seem to indicate that this should work... If the old database is 100MB and the new database is 50MB and I'm saying the journal file is small, then 80MB free before you start should be enough. Except... all that disk space isn't taken from the same spot. The temp database is opened as '' (e.g. a zero-length string), which creates it in the temporary space. Where, exactly, this is depends on the host OS. Unix likes /var/tmp, /usr/tmp/ or /tmp, for example. If /tmp is a different filesystem (as it often is), and if it isn't big enough to hold the new database, you're going to get a space error. So... Just to verify if this really is or isn't the journal file, you can try to turn the journal file off: PRAGMA journal_mode = OFF; I would do this on a test system. If the problem really is the journal file, this should allow things to work. If you still get a space error, we're dealing with something else. If that doesn't work, move the temporary files to some place that has enough space to hold the new database file. It should be OK to set this to the same directory as the original database PRAGMA temp_store_directory = '/path/to/database/directory'; If that filesystem has enough space to hold both the old database and the new database, you should be able to vacuum it correctly. Let us know how it works out. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users