My app uses sqlite 3.8.8. I am working on something to control the disk size of the app's sqlite database file since my app stores some media data and it is easy for it to use too much disk space.

I observed an interesting but weird behavior.

My app's database runs with WAL enabled, synchronous is NORMAL. In my C++ code, whenever it detects that the database file size (include .db, .db-wal, .db-shm) exceeds the limit, it deletes some old records in the table and then run wal_checkpoint(TRUNCATE), VACUUM and wal_checkpoint(TRUNCATE). After running VACUUM, I saw that the wal file's size becomes similar to the main database file's size. I guess this is expected since VACUUM writes the journey into the wal file. However, after running the last wal_checkpoint(TRUNCATE) command, the wal file's size doesn't change. I would expect that it goes to zero since it should truncate the wal file. Following is my sudo code:

database.exec("PRAGMA wal_checkpoint(TRUNCATE)")
checkAndPringDatabaseFileSize()
database.exec("VACUUM")
checkAndPringDatabaseFileSize()
database.exec("PRAGMA wal_checkpoint(TRUNCATE)")
checkAndPringDatabaseFileSize()

Could someone help me understand why PRAGMA wal_checkpoint(TRUNCATE) doesn't work after a VACUUM?

Thanks!

Just curious. I trust you are aware that 3.8.8 dates back to a release date of 16-Jan-2015?

Not sure if others can help you with a release that old.

rayB

In addition to rayB's more significant reply, I just want to make sure you are actually passng SQLITE_CHECKPOINT_TRUNCATE, not TRUNCATE.

Thanks for replying!

No. I used TRUNCATE because that is what the SQLite wiki says.

I guess I need to be more clear. In my c++ code, I'm not calling the sqlite3_wal_checkpoint_v2 api. I'm running the PRAGMA wal_checkpoint(TRUNCATE) command directly.

Ah, okay. That should work. Sorry for the pointless concern.