On Sun, Mar 14, 2010 at 07:19:59PM -0400, Matthew L. Creech scratched on the 
> 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

  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.


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

Reply via email to