My SQLite WAL file gets very large sometimes and I think that the file retains its size and just begins to overwrite as necessary, does it ever just reset its size?
I'm aware of the pragma wal_checkpoint(TRUNCATE) command to manually set a checkpoint that will trim the file size down to zero, but I have noticed some issues with this.
I start my writer and it creates a new database and begins writing data into it. At the beginning of this program I issue the wal_checkpoint command (obviously the first run since the DB doesn't have anything in it, it doesn't do anything). Readers are not interrupted at all. When I shut down the writer and restart it (it reconnects and continues with the existing DB), it clears the wal file as expected, but now (until I delete the DB and start over) when a reader reads from the DB, the writer gets SQLITE_BUSY exceptions - usually not always.
So I'm wondering will the WAL truncate itself at some point without me calling wal_checkpoint? And if anyone can answer: Why does it cause my SQLITE_BUSY exception above on any subsequent run after it has been called? Is there a better way than wal_checkpoint(Truncate)?
