107

I am seeing some strange behavior with my application and the state of its database file after running some tests that close the database, delete it, and replace it with a test fixture. When I examine the database file with a tool on my debugging PC, it doesn't match what the application itself seems to be reporting. It's possible that this strange behavior is related to this bug.

I noticed that there are two files with the same base name as the database (with the normal .db extension.) The file extensions are .db-shm and .db-wal, and each is newer than the .db file's timestamp.

I assume that these are some type of temporary files. However, I am wondering if the application is terminated, shouldn't they be deleted? More importantly, I assume whatever data is stored in them is updated inside the .db file before the application is terminated by the operating system. Is this correct?

Jeff Axelrod
  • 27,676
  • 31
  • 147
  • 246
  • @satur9nine that's really odd.. I was asked to update someone else's code.. and during testing i realized that his use of core data worked just fine with iOS 6.x.. but then when I tested it on iOS 7.x.. it resulted in the above `.db-shm` and `.db-wal`.. did this happen to anyone else? – abbood Nov 11 '13 at 10:59
  • 3
    On iOS 7 these appear to contain all the transactions. The db file is an empty database for me. I need to grab all three files to see the "real" contents of the db. – Ryan Booker Jan 15 '14 at 03:39

3 Answers3

57

You are correct, these are temporary files created by SQLite. If you are manually deleting the main db you should probably delete these too. From what I can gather the WAL is a replacement for the rollback journal that enables SQLite to rollback changes when a transaction fails. How SQLite uses them and why they are kept around for so long is up to the authors of SQLite but in general SQLite seems pretty rock solid so I wouldn't worry too much about them. For more info take a look here:

http://www.sqlite.org/fileformat2.html#walindexformat

These files are a new feature of SQLite 3.7. I'm not sure if their existence relates to the bug you point out but the bug report suggests a work-around anyway.

UPDATE:

Better documentation about the WAL is here:

https://www.sqlite.org/wal.html

The contents of the WAL are periodically moved to the DB file but this is not guaranteed to occur each time the process exits. Thus when WAL is enabled each SQLite DB consists of two files on disk that must be preserved, both the .db file and the .db-wal file.

The .db-shm file is a shared memory file that contains only temporary data.

satur9nine
  • 13,927
  • 5
  • 80
  • 123
  • 3
    But what about the .shm file. Can you throw some light on that? – G.Abhisek Sep 20 '16 at 12:27
  • 1
    Per the link: The mmapped file is in the same directory as the database and has the same name as the database with a "-shm" suffix appended. – satur9nine Sep 26 '17 at 19:51
  • 1
    I still don't understand what they do *exactly*, but it seems that I can't view the contents of .db file without them. Are they any kind of metadata? – getsadzeg Apr 24 '19 at 11:38
  • 3
    On android I would like to warn that that if you are moving your database to a different folder, you MUST also move the -wal and -shm files. Otherwise your database could be empty. This problem arose on the emulator at least. – Carson Holzheimer Jan 22 '20 at 06:23
31

I do not yet have enough reputation to just add a comment to satur9nine's answer, so I'll pile on here.

As per the SQLite docs, the DB-SHM file is a Shared Memory file, only present when SQLite it running in WAL (Write-Ahead Log) mode. This is because in WAL mode, db connections sharing the same db file must all update the same memory location used as index for the WAL file, to prevent conflicts.

As for WAL file, as hinted above, it is a write log/journal, useful for commits/rollback purposes.

Lutz Prechelt
  • 36,608
  • 11
  • 63
  • 88
Tiede
  • 419
  • 4
  • 4
  • 7
    From the [documentation](https://www.sqlite.org/wal.html) (section 4): "The WAL file is part of the persistent state of the database and should be kept with the database if the database is copied or moved". Manually deleting this file may cause data loss! – luator Jun 08 '21 at 15:22
2

Make sure that you have closed cursor properly into SELECT operation. Sometimes SQLiteOpenHelper creates .db-shm and .db-wal extensions database due to unclosed Cursor.

Simas Joneliunas
  • 2,890
  • 20
  • 28
  • 35
Sayantan Basu
  • 39
  • 9