32

With WAL (Write-Ahead-Logging) enabled in SQLite 3.7 (which is the default for Core Data on iOS 7), how do I merge/commit the content from the -wal file back into the main database file?

Johannes Fahrenkrug
  • 42,912
  • 19
  • 126
  • 165

4 Answers4

46

From the command line, do this:

  1. sqlite3 MyDatabase.sqlite
  2. VACUUM;
  3. CTRL-D to exit the sqlite console.

Done!

The -wal file should now have a size of 0 and everything should be in your main database file.

Johannes Fahrenkrug
  • 42,912
  • 19
  • 126
  • 165
  • 4
    There are two solutions proposed in this thread: (1) PRAGMA wal_checkpoint (2) VACUUM and exit. I too am working with Core Data. My main sqlite file was 46 MB and my -wal file was a whopping 1.2 GB. My results: PRAGMA wal_checkpoint or PRAGMA wal_checkpoint(1) have no effect. VACUUM reduced the size of the -wal file from 1.2 GB to 40.8 MB, and reduced the main sqlite from from 46 to 40.5 MB. Repeated executions of either command did not result in any further reductions. However, exitting the sqlite3 console reduced the -wal file to 0, as stated by Johannes. – Jerry Krinock Aug 17 '15 at 18:41
  • @JerryKrinock Hey Jerry, good to hear from you. That's really interesting! You might want to ask people on the sqlite mailing list about that and please update your comment when you find out why that's happening. – Johannes Fahrenkrug Aug 17 '15 at 19:34
  • 6
    `sqlite3 MyDatabase.sqlite vacuum` for short :) – mortalis Sep 21 '20 at 10:40
  • 1
    FYI: `VACUUM` rebuilds the database file to reduce it's size. https://www.sqlite.org/lang_vacuum.html – jl005 Apr 25 '21 at 23:22
40

Do a checkpoint, i.e., execute PRAGMA wal_checkpoint.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • 1
    On terminal run `sqlite3 db.sqlite` make sure that the directory contains `-wal` file then run `PRAGMA wal_checkpoint` and exit the db by `ctrl+d` – Inder Kumar Rathore Feb 15 '17 at 17:07
4

In an app "DB Browser for SQLite" open a database and switch between Journal Mode from "WAL" to "Off" and tap a button "Apply", and switch back to "WAL".

Blazej SLEBODA
  • 8,936
  • 7
  • 53
  • 93
0

I had tried VACUUM and Checkpoint options, none of them were working for me.

@Blazej answer guided me to the right solution. To summarize you need to do the following

Pre-requisite : Make sure that the directory contains both .sqlite file and the -wal file.

Option 1: Using sqlite3 command

Type the following commands from terminal. Make sure that the database is not connected from your App (close your application), or any external app eg: 'DB Browser for SQLite'. If the database is connected from more than one service, the database will be locked, you cannot change its settings.

  1. sqlite3 <database_name>.sqlite

This will show you 'sqlite>' prompt.

  1. PRAGMA journal_mode = OFF;

Now you can see the -wal file disappeared from your directory, and the the main database .sqlite file size increased.

  1. PRAGMA journal_mode=WAL;

This will change the data base file journal_mode back to WAL.

Option 2: Using 'DB Browser for SQLite' app

Make sure that the database is not connected from your App (close your application), or any other services like sqlite3 command. If the database is connected from more than one service, the database will be locked, you cannot change its settings.

  1. Launch 'DB Browser for SQLite' and open the data base file.

  2. Go to 'Edit Pragmas' -> 'Journal mode' and set its value to 'OFF'

Now you can see the -wal file disappeared from your directory, and the the main database .sqlite file size increased.

  1. Change the settings back, Go to 'Edit Pragmas' -> 'Journal mode' and set its value to 'WAL'
Shihab
  • 111
  • 4