What is the "-journal" SQLite database in Android?

Question

In my andoid application's database directory (/data/data/com.me.myApp/databases), for every sqlite database I create there is a corresponding database of the same name with "-journal" appended to it's name.

e.g: myDatabase, myDatabase-journal, myOtherDatabase.db, myOtherDatabase.db-journal

What is this?

and,

If I'm providing pre-filled databases for my app (as per: http://www.reigndesign.com/blog/using-your-own-sqlite-database-in-android-applications/) do I need to include these as well?'

score 36 · Accepted Answer · edited Jun 20 '20 at 09:12

Such -journal files do not need to (and should not) be distributed.

This is because the various journal files represent temporary data (ref. SQLite's Use Of Temporary Disk Files) used by SQLite. In particular a -journal file is a rollback journal.

A rollback journal is a temporary file used to implement atomic commit and rollback capabilities in SQLite. (For a detailed discussion of how this works, see the separate document titled Atomic Commit In SQLite.) The rollback journal is always located in the same directory as the database file and has the same name as the database file except with the 8 characters "-journal" appended.

The rollback journal is usually created when a transaction is first started and is usually deleted when a transaction commits or rolls back. The rollback journal file is essential for implementing the atomic commit and rollback capabilities of SQLite. Without a rollback journal, SQLite would be unable to rollback an incomplete transaction, and if a crash or power loss occurred in the middle of a transaction the entire database would likely go corrupt without a rollback journal.

In general these -journal files should only exist when there is an open SQLite database - or rather, a running transaction - but can be controlled via PRAGMA journal_mode. With default pragma settings the -journal files will be deleted.

The DELETE journaling mode is the normal behavior. In the DELETE mode, the rollback journal is deleted at the conclusion of each transaction. Indeed, the delete operation is the action that causes the transaction to commit.

Make sure to only copy the actual database files when the database is not opened and all journals have been deleted (or cleared) by SQLite itself; this implies all transactions have been completed and the database is in a consistent state.

What if the -journal file is consistently present and non-trivial size (e.g. 30kB), yet one's app is not performing any activity to the database, and doesn't leave databases open? — Michael, Dec 02 '15 at 06:29
@Michael That is a good question - but I'm afraid it will be buried in the comment, as I don't have an immediate expert knowledge answer. Consider creating another SO question, linking to this (and other) questions/answers to present a good foundation. — user2864740, Dec 02 '15 at 22:26
@Michael I'm sorry, I'm afraid I arrived maybe too late here, but I've got some interesting info [here](http://sandersonforensics.com/forum/content.php?208-Dealing-with-records-found-in-SQLite-Rollback-Journals). It says that when a transaction is committed, the header is usually zeroed. At every commit, the header is written, then the pages are written, overwriting the original content, then only the header is zeroed when success. A general solution to 'clean' (e.g. remove) the file, if another commit doesn't do it, could be to execute VACUUM in the DB. — Lutarisco, Feb 26 '17 at 15:00

score 0 · Answer 2 · edited May 08 '21 at 20:27

0

A DB-JOURNAL file is a temporary database file created by SQLite database management systems during a transaction between an application and a database. It contains a rollback journal, which is a temporary database that stores the most recent state of the database.

edited May 08 '21 at 20:27

jkmartindale

523
2
9
22

answered Apr 14 '21 at 01:26

Meraj

439
3
9

score 0 · Answer 3 · edited Nov 26 '21 at 07:09

Is the problem solved in this? I have a case where I can not copy the .db I want to copy on my local machine from the android device. I have to manually delete the -journal on the device to successfully copy the .db. This is my code written in C#

var devices = MediaDevice.GetDevices();
using (var device = devices.First(d => d.Description == "PM67"))
{
    device.Connect();
    var photoDir = device.GetDirectoryInfo(@"\Internal Shared Storage\Databases");

    var files = photoDir.EnumerateFiles("database.db", SearchOption.AllDirectories);

    foreach (var file in files)
    {
        MemoryStream memoryStream = new System.IO.MemoryStream();
        device.DownloadFile(file.FullName, memoryStream);
        memoryStream.Position = 0;

        try
        {
            device.DeleteFile(@"\Internal Storage\Databases\database.db-journal");//this is the part where I'd like to delete the -journal but it's not working
        }
        catch
        {
        }

        try
        {
            if (File.Exists(result) == true)
            {
                File.Delete(result);
            }
        }
        catch
        {
        }
        WriteSreamToDisk(result, memoryStream);
    }
    device.Disconnect();
}

This is unclear, is this an answer providing a solution or are you asking for a solution with the given code not working? — Ruli, Nov 26 '21 at 06:58

What is the "-journal" SQLite database in Android?

3 Answers3

Linked