I have a long running .net app and I’ve started noticing the temporary WAL file that sqlite generates never seems to shrink and it’s starting to cause problems for me when getting to the GB+ range. I’ve searched and tried Purging documents, compacting the database, and even closing the database all together but to no avail. Am I doing something wrong?
Quick gist demonstrating problem:
The SQLite WAL is, by default, merged into the main database periodically when it reaches a threshold size, according to the docs. That size defaults to 4MB (assuming a 4K page size.) Also, the WAL is by default checkpointed and deleted when the database is [explicitly] closed.
It looks like you’re using .NET (?) On that platform I believe we use the .NET interface to SQLite, and I don’t know what its behavior is for checkpointing the WAL. @borrrden is the expert here…
The .NET interface to SQLite is as thin as it gets. There are one to one calls to the C API and so all of the default behavior should apply. It seems odd that even compacting wouldn’t help since it forces a WAL checkpoint. Is there some behavior that can prevent a WAL merge?
Well, the docs say:
A checkpoint operation takes content from the WAL file and transfers it back into the original database file. A checkpoint can run concurrently with readers, however the checkpoint must stop when it reaches a page in the WAL that is past the end mark of any current reader. The checkpoint has to stop at that point because otherwise it might overwrite part of the database file that the reader is actively using. […]
Thus a long-running read transaction can prevent a checkpointer from making progress. But presumably every read transaction will eventually end and the checkpointer will be able to continue.
Whenever a write operation occurs, the writer checks how much progress the checkpointer has made, and if the entire WAL has been transferred into the database and synced and if no readers are making use of the WAL, then the writer will rewind the WAL back to the beginning and start putting new transactions at the beginning of the WAL. This mechanism prevents a WAL file from growing without bound.
Sounds like we should file an issue on this, since @PMilla was kind enough to provide a simple reproducible case 
We can translate it to LiteCore too, to see if it’ll affect 2.0.
Here’s the issue.. Thanks for reporting this.
Great! Thanks for looking into this! 