SQLite WAL Checkpoints: Why Your Database File Keeps Growing

The exact behavior depends on the transactions and WAL state. Not every active reader blocks every checkpoint, but a reader that retains an old snapshot can prevent SQLite from fully checkpointing the WAL.

Common Causes of Long-Running Read Transactions

Typical causes include:

• Starting a transaction and forgetting to commit or roll it back.

• Processing a large result set while keeping the transaction open.

• Waiting for an HTTP request or external service inside a database transaction.

• Reusing a database connection without properly disposing of transaction objects.

• Running background jobs that perform lengthy processing while retaining a read snapshot.

For example, this application pattern is risky:

using var transaction = connection.BeginTransaction();

var orders = connection.Query<Order>(
    """
    SELECT Id, CustomerName, Status
    FROM Orders
    """,
    transaction: transaction
);

// Expensive processing occurs while the transaction remains open.
await SendOrdersToExternalServiceAsync(orders);

transaction.Commit();

The transaction remains open while the application waits for an external service. During that period, other connections may write to the database, while the old read snapshot can restrict checkpoint progress.

A better approach is to retrieve the required data, finish the database transaction, and then perform external processing.

For example:

List<Order> orders;

using (var connection = new SqliteConnection(connectionString))
{
    connection.Open();

    orders = connection.Query<Order>(
        """
        SELECT Id, CustomerName, Status
        FROM Orders
        WHERE Status = 'Pending'
        """).ToList();
}

// Database work has completed before external processing begins.
await SendOrdersToExternalServiceAsync(orders);

This example uses Dapper with Microsoft.Data.Sqlite. It assumes that the query does not need to participate in a larger explicit transaction. If the application requires transactional consistency across multiple database operations, keep those operations in a transaction, but avoid holding that transaction open during unrelated network or CPU-intensive work.

Monitoring WAL Checkpoint Progress

SQLite provides a checkpoint status interface through the wal_checkpoint pragma.

Run:

PRAGMA wal_checkpoint(PASSIVE);

The result contains three integer values:

1. A status value indicating whether the checkpoint was blocked or encountered an error condition.

2. The number of WAL frames.

3. The number of frames that were checkpointed.

For example, a result might look like this:

0 | 1200 | 1200

This indicates that the checkpoint was not reported as blocked and that the reported WAL frames were checkpointed.

A result such as:

0 | 1200 | 350

indicates that 350 of the 1,200 reported frames were checkpointed during that operation. The remaining frames may require further checkpointing or may be constrained by active database activity.

These values are a snapshot of checkpoint status, not a complete history of WAL growth. Repeated measurements are more useful than a single result.

Also, the pragma's status value should not be interpreted as a universal guarantee that every aspect of database health is normal. Checkpoint results must be considered alongside application errors, file sizes, transaction behavior, and SQLite's return codes.

Monitoring With C#

The following example runs a passive checkpoint and reads its reported values using Microsoft.Data.Sqlite:

using Microsoft.Data.Sqlite;

var connectionString = "Data Source=app.db";

using var connection = new SqliteConnection(connectionString);
connection.Open();

using var command = connection.CreateCommand();

command.CommandText = "PRAGMA wal_checkpoint(PASSIVE);";

using var reader = command.ExecuteReader();

if (reader.Read())
{
    var status = reader.GetInt32(0);
    var walFrames = reader.GetInt32(1);
    var checkpointedFrames = reader.GetInt32(2);

    Console.WriteLine($"Checkpoint status: {status}");
    Console.WriteLine($"WAL frames: {walFrames}");
    Console.WriteLine($"Checkpointed frames: {checkpointedFrames}");

    if (walFrames > checkpointedFrames)
    {
        Console.WriteLine(
            "Some WAL frames were not checkpointed during this operation.");
    }
}

This is a diagnostic example, not a complete checkpoint management service. A production application should also handle database exceptions, record measurements over time, and avoid running checkpoint operations too frequently.

If a checkpoint reports incomplete progress repeatedly, investigate active transactions and database access patterns before attempting increasingly aggressive checkpoints.

Configuring Automatic Checkpointing

SQLite normally performs automatic checkpointing when the WAL reaches a configured threshold. The default automatic checkpoint threshold is commonly 1,000 WAL pages.

The threshold is measured in pages, not bytes. Its approximate size in bytes depends on the database page size.

For example, if the page size is 4,096 bytes, 1,000 pages correspond to approximately 4 MB of WAL page content. The actual file size can differ because of WAL headers, frame headers, and other file details.

You can inspect the current database page size:

PRAGMA page_size;

You can configure the automatic checkpoint threshold:

PRAGMA wal_autocheckpoint = 1000;

This configures the connection's automatic checkpoint threshold. The value is expressed in pages.

A lower threshold may trigger checkpointing more frequently, potentially keeping WAL growth smaller under some workloads. However, frequent checkpointing can increase write-related overhead.

A higher threshold can reduce checkpoint frequency but allow the WAL to grow larger before automatic checkpointing is triggered.

Important Connection-Level Considerations

SQLite settings do not all have the same scope. Some settings are stored in the database file, while others apply to an individual connection.

For example, journal_mode = WAL persists as the database's journal mode. By contrast, wal_autocheckpoint is configured for a connection.

If an application creates multiple connections, do not assume that executing PRAGMA wal_autocheckpoint on one connection automatically configures every other connection.

Configure connection-specific settings consistently where required, and verify the behavior of the SQLite library and connection pool used by the application.

Common Mistakes That Make WAL Growth Worse

Assuming That Every Large WAL File Indicates a Leak

A large WAL file can be normal after a burst of writes. SQLite may retain the file for reuse even after checkpointing.

Instead of relying on file size alone, monitor whether the file keeps growing, whether checkpoints progress, and whether disk usage exceeds operational limits.

Forcing a Truncate Checkpoint After Every Write

Calling PRAGMA wal_checkpoint(TRUNCATE) after every insert or update can introduce unnecessary coordination and delays.

A checkpoint is not required after each committed transaction. Let SQLite's automatic checkpointing handle ordinary workloads unless measurements show that a different strategy is necessary.

Keeping Transactions Open During External Operations

Database transactions should generally cover the database operations that require transactional consistency. Waiting for remote APIs, processing large files, or performing lengthy business logic inside a transaction can unnecessarily extend snapshot lifetimes or retain locks.

Complete database work before starting unrelated operations whenever the application's consistency requirements allow it.

Deleting the WAL File Manually

Do not manually delete an active -wal file to reduce disk usage.

The WAL can contain committed database changes that have not yet been incorporated into the main database file. Removing it while the database is in use can cause data loss or database corruption.

Use SQLite's checkpointing mechanisms and supported connection-management procedures instead.

Treating the Shared-Memory File as the Main Problem

The -shm file is used for coordination between WAL connections. Its presence does not itself indicate that checkpointing has failed.

Investigate the WAL file, checkpoint results, transaction lifetimes, and actual storage consumption rather than deleting SQLite's supporting files.

Advantages and Disadvantages of WAL Checkpointing

Advantages

• Keeps committed changes from accumulating indefinitely in the WAL under normal conditions.

• Allows SQLite to transfer database pages into the main database file.

• Supports continued WAL reuse, reducing the need to repeatedly allocate new storage.

• Provides different checkpoint modes for routine operation and maintenance.

• Helps applications manage disk usage and diagnose transaction-related bottlenecks.

Disadvantages

• Checkpoints may be delayed or incomplete when active readers require older database snapshots.

• Aggressive checkpointing can interfere with application responsiveness.

• A successful checkpoint does not always cause an immediate reduction in physical WAL file size.

• Monitoring requires understanding both frame counts and file-system measurements.

• Applications must coordinate maintenance with active database connections when truncation is required.

Troubleshooting Checklist for Production Applications

When a SQLite WAL file keeps growing, investigate the problem systematically.

1. Confirm that the database is operating in WAL mode by running PRAGMA journal_mode;.

2. Record the sizes of the main database, WAL, and shared-memory files over time.

3. Run PRAGMA wal_checkpoint(PASSIVE); and inspect the returned status and frame counts.

4. Check for long-running transactions, unfinalized statements, and connections that remain open unnecessarily.

5. Review whether the application performs lengthy external operations while a database transaction is active.

6. Verify the automatic checkpoint configuration on the connections used by the application.

7. Check available disk space and determine whether WAL growth corresponds to a temporary write burst or a persistent pattern.

8. If the application needs to reclaim WAL file space, schedule an appropriate checkpoint operation when database activity permits it.

9. Test the checkpoint strategy under realistic concurrent read and write workloads before deploying configuration changes.

If the WAL remains large despite repeated successful checkpointing, determine whether SQLite is retaining the file for reuse. A stable, reusable WAL file is different from one that continues accumulating uncheckpointed changes.

Best Practices for SQLite WAL Management

For applications using SQLite in production, follow these practices:

• Keep read and write transactions as short as practical.

• Dispose of database commands, readers, transactions, and connections correctly.

• Avoid waiting for external services while holding database transactions open.

• Use automatic checkpointing as the baseline rather than forcing checkpoints after every write.

• Monitor checkpoint progress and file-size trends together.

• Choose PASSIVE, FULL, RESTART, or TRUNCATE according to the operational requirement.

• Do not delete WAL or shared-memory files manually while the database is active.

• Test checkpoint behavior with the same SQLite version, library, and connection-management strategy used in production.

• Ensure that backup procedures are compatible with WAL mode and capture a consistent database state.

Conclusion

SQLite WAL checkpointing is an important part of managing database storage and concurrency. The WAL file records committed changes before they are incorporated into the main database, and checkpoints transfer eligible changes back into the database file.

When the WAL grows unexpectedly, the cause may be a high write volume, incomplete checkpoint progress, a long-running reader, or simply SQLite retaining the WAL file for reuse. File size alone is not enough to identify the problem.

Start by monitoring checkpoint results, reviewing transaction lifetimes, and checking automatic checkpoint configuration. Use aggressive checkpoint modes only when the application needs them and database activity permits them.

With the right monitoring and transaction-management practices, developers can keep SQLite storage behavior predictable without introducing unnecessary checkpoint overhead or risking database integrity.