Unexpected SQLite Database File Splitting into 2GB Chunks with .001, .002 Extensions

Understanding Sudden Database File Splitting into 2GB Chunks

1. Core Symptoms and Environmental Context

The primary issue involves an SQLite database (my_database.db) unexpectedly splitting into multiple files with extensions like .001, .002, each approximately 2GB in size. These files are not valid SQLite databases and appear sporadically during operations involving heavy INSERT, REPLACE, and DELETE queries on a single table. The database size typically remains under 1.5GB, but under certain conditions, it balloons to nearly 2GB, triggering the creation of additional chunked files.

Key environmental factors include:

• Use of SQLite’s C/C++ API (version 3.36.0).
• Specific PRAGMA settings:
  • journal_mode=OFF (disables rollback/journal files).
  • locking_mode=EXCLUSIVE (exclusive file locks).
  • synchronous=FULL (ensures writes are flushed to disk).
  • temp_store=2 (stores temporary objects in memory).
• Intentional avoidance of auxiliary SQLite files (e.g., journals, WAL files).
• Occasional database corruption due to abrupt process termination (e.g., crashes, forced halts during writes).

The sporadic nature of the issue complicates reproducibility, but the 2GB threshold and chunked filenames (.001, .002) strongly hint at SQLite’s multiplex VFS subsystem. This subsystem is designed for environments where the underlying filesystem cannot handle files larger than 2GB. When enabled, it splits databases into 2GB chunks, creating numbered extensions automatically.

2. Root Causes of File Splitting and Corruption Risks

A. Accidental Multiplex VFS Initialization

The most definitive cause is the inadvertent activation of SQLite’s multiplex VFS (test_multiplex.c), which is not part of the standard SQLite library. This subsystem is explicitly designed to handle large databases on filesystems with 2GB file size limits by splitting the database into chunks. If your application (or a linked library) calls sqlite3_multiplex_initialize(), this VFS becomes the default handler for database files.

Why this happens accidentally:

• The multiplex VFS is sometimes included in custom SQLite builds for niche use cases.
• Third-party dependencies or frameworks might initialize it without explicit developer intent.
• Misunderstanding SQLite’s VFS layer configuration during application setup.

B. File Size Thresholds and Chunking Logic

The multiplex VFS uses SQLITE_MULTIPLEX_CHUNK_SIZE (2,147,418,112 bytes ≈ 2GB) as the maximum chunk size. When the primary database file (my_database.db) approaches this limit, the VFS creates a new chunk (my_database.001) and redirects subsequent writes to it. This process repeats indefinitely, creating .002, .003, etc., as needed.

Critical observations:

• Chunked files are not valid SQLite databases on their own—they are raw byte slices.
• The VFS layer transparently stitches chunks together during reads, but standard tools (e.g., sqlite3 CLI) cannot parse individual chunks.

C. Interaction with PRAGMA Settings

The configured PRAGMA values exacerbate the issue:

• journal_mode=OFF disables transaction rollback journals, which normally protect against corruption.
• locking_mode=EXCLUSIVE prevents concurrent access but does not inherently cause file splitting.
• synchronous=FULL ensures data durability but does not interact with the multiplex VFS directly.

Corruption risks:

• Abrupt process termination (e.g., crashes, debugger halts) during writes to a multiplexed database can leave chunks in inconsistent states.
• Without a journal, SQLite cannot recover incomplete transactions, leading to PRAGMA quick_check failures.

3. Resolving File Splitting and Preventing Corruption

Step 1: Disable the Multiplex VFS

Locate and remove multiplex initialization:

• Search your codebase for sqlite3_multiplex_initialize() and comment out or delete the call.
• If using a custom SQLite build, ensure SQLITE_EXTRA_DLL or similar compilation flags do not include test_multiplex.c.

Verify VFS configuration:

• After removing the initialization, check the active VFS using:

  sqlite3_vfs *vfs = sqlite3_vfs_find(NULL);
  printf("Active VFS: %s\n", vfs->zName);
  

• The default VFS should be unix (Linux/macOS) or win32 (Windows).

Step 2: Audit Third-Party Dependencies

• If your application uses libraries that interact with SQLite (e.g., ORMs, middleware), inspect their initialization routines for unintended VFS overrides.
• Recompile dependencies from source if necessary to exclude multiplex VFS components.

Step 3: Validate Database Integrity

After disabling the multiplex VFS, check for corruption caused by prior chunking:

PRAGMA quick_check;

• If corruption is detected, restore from a backup or attempt recovery using .recover in the sqlite3 CLI.

Step 4: Reconfigure PRAGMA Settings for Crash Safety

While journal_mode=OFF avoids journal files, it increases corruption risk. Consider alternatives:

• Enable Write-Ahead Logging (WAL):

  PRAGMA journal_mode=WAL;
  

  WAL uses a single write-ahead log file (my_database.db-wal) and offers better concurrency. However, it creates auxiliary files.

• Use journal_mode=DELETE (default):
  This creates temporary rollback journals but deletes them post-transaction.

Trade-off: If avoiding auxiliary files is non-negotiable, accept the higher corruption risk and implement manual safeguards:

• Regular backups.
• Graceful shutdown hooks to finalize pending writes.

Step 5: Monitor Database Size Proactively

To prevent unexpected 2GB growth:

• Optimize data storage: Use VACUUM to defragment the database.
• Enforce size limits: Implement application logic to archive or purge old records before hitting filesystem limits.

Step 6: Reproduce and Stress-Test

Force the issue in a controlled environment:

• Simulate heavy write loads with scripts performing bulk INSERT/DELETE operations.
• Use filesystem monitoring tools (e.g., inotify on Linux) to watch for chunk file creation.

Final Notes:
The sporadic file splitting stems solely from the multiplex VFS activation. By eliminating its initialization and adopting safer PRAGMA settings, you can maintain a single-database file while mitigating corruption risks. Always validate SQLite configuration in mission-critical deployments to avoid unintended subsystems altering default behaviors.