Search code examples
sqlitewal

What could be preventing SQLite WAL files from being cleared?

I've had three cases of WAL files growing to massive sizes of ~3 GB on two different machines with the same hardware and software setup. The database file itself is ~7 GB. Under normal runtime, WAL is either non-existent or a few kilobytes.

I know it's normal for WAL to grow as long as there's constantly a connection open, but even closing the application doesn't get rid of the WAL file or the shared memory file. Even restarting the machine, without ever starting my application, and attempting to open the database with DB Browser for SQLite doesn't help. It takes ages to launch (I don't know how long exactly, but definitely over 5 minutes) and then WAL and shared memory files remain, even after closing the browser.

Bizarrely, here's what did help. I zipped up the three files to investigate locally, but then on a hunch I deleted the originals and extracted the files back again. Launching the app took seconds and the extra files were gone just like that. The same thing happened on my laptop with the unzipped files. I ran the integrity check on my machine and the database seems to be fine.

Here's the rough description of the app, if it means anything.

  • EF Core 2.1.14 (it uses SQLite 3.28.0)
  • .Net Framework 4.7.1
  • except for some rare short-lived overlap with read-only access, only one thread accesses the database
  • entire connection (DbContext) gets closed roughly every 2 seconds max
  • shared memory is used with PRAGMA mmap_size = 268435456 (256 MiB)
  • synchronous mode is NORMAL
  • the OS is Windows 7

Any ideas what might be causing this... quirk?

Solution

  • I temporarily switched to journal mode, which helpfully reported errors instead of silently failing.

    The error I was having was SQLITE_IOERR_WRITE. After more digging, it turned out the root Windows error causing all this was 665 - hitting into an NTFS file system limitation. An answer here then led me to the actual cause: extreme file fragmentation of the database.

    Copying the file reduces fragmentation, which is why the bizarre fix I mentioned, copying the file, temporarily worked. The actual fix was to schedule defragmentation using Sysinternals' Contig.