1

I have a test.db an sqlite3 database. auto_vacuum=incremental however, when I call PRAGMA incremental_vacuum(1000) after deleting a huge amount of the database, the incremental_vacuum only remove the size of one page -4096B or 4K and I can't figure out why it does that.

Is there a limit cap on how many pages to be removed?

Is there a compile_option that I don't know off that has to be changed?

All ideas are welcome.

MohammedAlSafwan
  • 872
  • 1
  • 8
  • 25

2 Answers2

0

You did not mention how you access the SQLite database. One of possible reasons may be that your code uses (directly or indirectly via some wrapper library) the sqlite3_step API, which vacuums 1 page per step.

If this is the case, then you need to either loop until sqlite3_step() returns SQLITE_DONE (instead of SQLITE_ROW), or switch to using the sqlite3_exec wrapper instead.

Please see the possibly similar question.

Alex Che
  • 6,659
  • 4
  • 44
  • 53
-1

You should try using the VACUUM command, this will rebuild the database omitting deleted data.

Using PRAGMA incremental_vacuum(1000) will only release pages that are in the free list (you can use PRAGMA freelist_count to obtain the number of pages in the free list).

MikeT
  • 51,415
  • 16
  • 49
  • 68