How to fix SQLITE_BUSY_RECOVERY: Another process is recovering a WAL mode database in SQLite

# Linux/macOS: Find processes with the database file open
lsof /path/to/your/database.db

# Check for SQLite processes specifically
ps aux | grep -i sqlite

# Look for processes with the WAL file open
lsof /path/to/your/database.db-wal

# Use Handle.exe from Sysinternals
# handle.exe database.db

# Or use PowerShell to find processes
Get-Process | Where-Object {$_.Path -like "*sqlite*"}

# Check for lock on database files
.Handle64.exe -a database.db

# Use sqlite3 command line to check WAL status
sqlite3 /path/to/database.db "PRAGMA wal_checkpoint;"
sqlite3 /path/to/database.db "SELECT * FROM pragma_wal_checkpoint;"

import sqlite3
import time

def connect_with_retry(db_path, max_retries=5, initial_delay=0.1):
    """Connect to SQLite database with retry for BUSY_RECOVERY errors."""
    for attempt in range(max_retries):
        try:
            conn = sqlite3.connect(db_path)
            # Enable WAL mode if not already enabled
            conn.execute("PRAGMA journal_mode = WAL;")
            return conn
        except sqlite3.OperationalError as e:
            if "SQLITE_BUSY_RECOVERY" in str(e) and attempt < max_retries - 1:
                delay = initial_delay * (2 ** attempt)  # Exponential backoff
                print(f"Database recovery in progress, waiting {delay:.2f}s (attempt {attempt + 1}/{max_retries})")
                time.sleep(delay)
                continue
            raise

    raise sqlite3.OperationalError(f"Failed to connect after {max_retries} retries")

# Usage
try:
    conn = connect_with_retry("database.db")
    # Use connection...
    conn.close()
except sqlite3.OperationalError as e:
    print(f"Connection failed: {e}")

const Database = require('better-sqlite3');

async function connectWithRetry(dbPath, maxRetries = 5, initialDelay = 100) {
    for (let attempt = 0; attempt < maxRetries; attempt++) {
        try {
            const db = new Database(dbPath);
            // Enable WAL mode
            db.pragma('journal_mode = WAL');
            return db;
        } catch (error) {
            if (error.code === 'SQLITE_BUSY_RECOVERY' && attempt < maxRetries - 1) {
                const delay = initialDelay * Math.pow(2, attempt);
                console.log('Database recovery in progress, waiting ' + delay + 'ms (attempt ' + (attempt + 1) + '/' + maxRetries + ')');
                await new Promise(resolve => setTimeout(resolve, delay));
                continue;
            }
            throw error;
        }
    }
    throw new Error('Failed to connect after ' + maxRetries + ' retries');
}

// Usage
try {
    const db = await connectWithRetry('database.db');
    // Use database...
    db.close();
} catch (error) {
    console.error('Connection failed:', error.message);
}

# Connect to database and force checkpoint
sqlite3 /path/to/database.db <<EOF
PRAGMA wal_checkpoint(FULL);
PRAGMA wal_checkpoint(TRUNCATE);
EOF

# Check WAL file size before and after
ls -lh /path/to/database.db-wal

# If WAL file is large, checkpoint may take time
# Monitor progress with:
sqlite3 /path/to/database.db "PRAGMA wal_checkpoint(PASSIVE);"

import sqlite3
import os

def force_checkpoint(db_path):
    """Force WAL checkpoint and wait for completion."""
    # First try passive checkpoint
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    # Get current WAL mode
    cursor.execute("PRAGMA journal_mode;")
    mode = cursor.fetchone()[0]

    if mode.upper() != "WAL":
        print(f"Database not in WAL mode: {mode}")
        conn.close()
        return

    # Try passive checkpoint first (non-blocking)
    cursor.execute("PRAGMA wal_checkpoint(PASSIVE);")
    result = cursor.fetchone()
    print(f"Passive checkpoint: busy={result[0]}, log={result[1]}, checkpointed={result[2]}")

    # If busy, try full checkpoint
    if result[0] != 0:  # busy
        print("Database busy, trying full checkpoint...")
        cursor.execute("PRAGMA wal_checkpoint(FULL);")
        result = cursor.fetchone()
        print(f"Full checkpoint: busy={result[0]}, log={result[1]}, checkpointed={result[2]}")

    conn.close()

    # Check WAL file size
    wal_path = db_path + "-wal"
    if os.path.exists(wal_path):
        size = os.path.getsize(wal_path)
        print(f"WAL file size: {size} bytes")

    return result

# Usage
force_checkpoint("database.db")

-- Default is 1000 pages (~4MB), reduce for faster checkpoints
PRAGMA wal_autocheckpoint = 100;  -- Checkpoint every 100 pages (~400KB)

-- Or disable auto-checkpoint and manage manually
PRAGMA wal_autocheckpoint = 0;

-- Default is 0ms, increase to allow recovery to complete
PRAGMA busy_timeout = 10000;  -- 10 seconds

-- Limit maximum WAL file size to prevent long recoveries
PRAGMA journal_size_limit = 1048576;  -- 1MB maximum WAL size

-- Set page size (default 4096, can be 512-65536)
PRAGMA page_size = 4096;

-- NORMAL (default): Balance between performance and safety
-- FULL: Safest, but slower
PRAGMA synchronous = NORMAL;

# Python example with comprehensive WAL setup
import sqlite3

def setup_sqlite_connection(db_path):
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    # Enable WAL mode
    cursor.execute("PRAGMA journal_mode = WAL;")

    # Configure for multi-process access
    cursor.execute("PRAGMA busy_timeout = 5000;")  # 5 second timeout
    cursor.execute("PRAGMA wal_autocheckpoint = 100;")  # Frequent checkpoints
    cursor.execute("PRAGMA journal_size_limit = 2097152;")  # 2MB max WAL
    cursor.execute("PRAGMA synchronous = NORMAL;")

    # Perform initial checkpoint
    cursor.execute("PRAGMA wal_checkpoint(TRUNCATE);")

    return conn

import sqlite3
import atexit
import signal
import os

class SafeSQLiteConnection:
    def __init__(self, db_path):
        self.db_path = db_path
        self.conn = None
        self.setup_signal_handlers()

    def setup_signal_handlers(self):
        """Register cleanup handlers for signals."""
        signal.signal(signal.SIGINT, self.signal_handler)
        signal.signal(signal.SIGTERM, self.signal_handler)
        atexit.register(self.cleanup)

    def signal_handler(self, signum, frame):
        """Handle termination signals."""
        print(f"Received signal {signum}, cleaning up database...")
        self.cleanup()
        os._exit(0)

    def cleanup(self):
        """Safely close database connection with checkpoint."""
        if self.conn:
            try:
                # Perform final checkpoint
                cursor = self.conn.cursor()
                cursor.execute("PRAGMA wal_checkpoint(TRUNCATE);")
                self.conn.close()
                print("Database connection closed safely with checkpoint")
            except Exception as e:
                print(f"Error during database cleanup: {e}")
            finally:
                self.conn = None

    def __enter__(self):
        """Enter context manager."""
        self.conn = sqlite3.connect(self.db_path)
        self.conn.execute("PRAGMA journal_mode = WAL;")
        self.conn.execute("PRAGMA busy_timeout = 5000;")
        return self.conn

    def __exit__(self, exc_type, exc_val, exc_tb):
        """Exit context manager with cleanup."""
        self.cleanup()

# Usage
with SafeSQLiteConnection("database.db") as conn:
    cursor = conn.cursor()
    cursor.execute("INSERT INTO data (value) VALUES (?)", ("test",))
    conn.commit()
# Connection automatically cleaned up on exit

const Database = require('better-sqlite3');

class SafeSQLiteDB {
    constructor(dbPath) {
        this.dbPath = dbPath;
        this.db = null;
        this.setupCleanupHandlers();
    }

    setupCleanupHandlers() {
        // Handle process termination
        process.on('SIGINT', () => this.cleanup());
        process.on('SIGTERM', () => this.cleanup());
        process.on('exit', () => this.cleanup());
    }

    cleanup() {
        if (this.db) {
            try {
                // Perform final checkpoint
                this.db.pragma('wal_checkpoint(TRUNCATE)');
                this.db.close();
                console.log('Database connection closed safely');
            } catch (error) {
                console.error('Error during database cleanup:', error.message);
            } finally {
                this.db = null;
            }
        }
    }

    getConnection() {
        if (!this.db) {
            this.db = new Database(this.dbPath);
            this.db.pragma('journal_mode = WAL');
            this.db.pragma('busy_timeout = 5000');
        }
        return this.db;
    }
}

// Usage
const safeDB = new SafeSQLiteDB('database.db');
const db = safeDB.getConnection();
// Use db...
// Cleanup happens automatically on process exit

-- Check current WAL mode
PRAGMA journal_mode;

-- Get WAL file information
PRAGMA wal_checkpoint;  -- Returns [busy, log, checkpointed]

-- Check WAL statistics
SELECT * FROM pragma_wal_checkpoint;

-- Monitor database locks
SELECT * FROM pragma_lock_status;

-- Check connection count
PRAGMA database_list;

import sqlite3
import os
import time

def monitor_wal_status(db_path, interval=5):
    """Monitor WAL status periodically."""
    wal_path = db_path + "-wal"
    shm_path = db_path + "-shm"

    while True:
        try:
            conn = sqlite3.connect(db_path, timeout=1)
            cursor = conn.cursor()

            # Get WAL mode
            cursor.execute("PRAGMA journal_mode;")
            mode = cursor.fetchone()[0]

            # Get checkpoint status
            cursor.execute("PRAGMA wal_checkpoint(PASSIVE);")
            busy, log_size, checkpointed = cursor.fetchone()

            # Get file sizes
            wal_size = os.path.getsize(wal_path) if os.path.exists(wal_path) else 0
            shm_size = os.path.getsize(shm_path) if os.path.exists(shm_path) else 0

            print(f"[{time.strftime('%H:%M:%S')}] "
                  f"Mode: {mode}, "
                  f"Busy: {busy}, "
                  f"WAL pages: {log_size}, "
                  f"Checkpointed: {checkpointed}, "
                  f"WAL size: {wal_size:,} bytes, "
                  f"SHM size: {shm_size:,} bytes")

            conn.close()

            if busy:
                print("  WARNING: Database is busy with recovery/checkpoint")

            time.sleep(interval)

        except sqlite3.OperationalError as e:
            print(f"Error: {e}")
            time.sleep(interval)
        except KeyboardInterrupt:
            print("Monitoring stopped")
            break

# Usage
monitor_wal_status("database.db")

#!/bin/bash
# recover_sqlite.sh

DB_PATH="$1"
TIMEOUT=30

echo "Attempting to recover SQLite database: $DB_PATH"

# Try to connect with timeout
timeout $TIMEOUT sqlite3 "$DB_PATH" "PRAGMA wal_checkpoint(FULL);" 2>/dev/null

if [ $? -eq 0 ]; then
    echo "Recovery successful"
else
    echo "Recovery failed or timed out after ${TIMEOUT}s"

    # Backup and recreate if recovery fails
    BACKUP="${DB_PATH}.backup.$(date +%s)"
    echo "Creating backup: $BACKUP"
    sqlite3 "$DB_PATH" ".backup '$BACKUP'" 2>/dev/null

    if [ $? -eq 0 ]; then
        echo "Backup created successfully"
    else
        echo "Backup failed"
    fi
fi