SQLite Forum

Growing WAL-File

Growing WAL-File

(1) By Heribert (derhexer) on 2021-04-06 07:53:27 [link] [source]

I'm using sqlite with WAL in my logging system. Currently the WAL-File is about 100GB and it continues growing.

There is no open transaction, but i have a lot of prepared statements (to improve speed).

Is there a way to limit the max. size of the WAL-File?

(2) By Kees Nuyt (knu) on 2021-04-06 15:52:15 in reply to 1 [link] [source]

You can checkpoint the WAL with PRAGMA wal_checkpoint().

There is even a PRAGMA to automatically checkpoint the WAL. Use that PRAGMA just after you open the connection, or call the equivalent sqlite3_API .

Make sure to preiodically COMMIT or ROLLBACK any open transactions.

(3) By Heribert (derhexer) on 2021-04-06 15:59:02 in reply to 2 [link] [source]

What happens if i do not use any transaction? 
In my case, I don't need transactions as I'm just adding log entries.

(4) By Richard Hipp (drh) on 2021-04-06 17:11:05 in reply to 3 [source]

A transaction is created automatically every time you write to the database. That transaction closes when the last prepared statement is reset (using sqlite3_reset()) or finalized (via sqlite3_finalize()). If you don't invoke sqlite3_reset() on every prepared statement that you run, then you will likely be leaving some transactions open, which will prevent the checkpoint from happening and resetting the WAL file.

(5) By Heribert (derhexer) on 2021-04-06 18:05:54 in reply to 4 [link] [source]

Ok. Thanks. So, i will check if do really reset all of them.

(6.2) By Keith Medcalf (kmedcalf) on 2021-04-07 01:34:07 edited from 6.1 in reply to 5 [link] [source]

You did not describe your transactions in much detail. Note that if you have a single thread of execution performing updates / selects, then, unless you have left a transaction hanging open (failed to run a statement until it returns SQLITE_DONE) then the maximum size of the WAL file is roughly bounded by the number of pages specified for the auto checkpoint size plus the size of the last transaction (in pages) to have ever triggered an auto checkpoint in the current session.

If, however, you are using multiple threads of execution (which includes multiple processes) then it is mayhaps a possibility that there is no time at which (a) an auto checkpoint occurs and (b) there are no transactions in progress, which will cause the WAL file to increase in size forever since it might never be able to checkpoint to the last page in the log.

In any case, this may lead to very large WAL files even though only a small portion of the WAL pages are in use.

You may use the pragma journal_size_limit to force the truncation of the WAL file to a specific size whenever a checkpoint (or auto checkpoint) succeeds in clearing (resetting) the log in order to limit the size of the residual file. Normally, the WAL file is re-cycled (overwritten) when reset and is not truncated so it will stay at the maximum size used during a session for the entire session even if the condition which caused the massive growth was transient.