How to Shrink and Clean Up Your SQLite Database in n8n: A Step-by-Step Guide

If you’re using n8n for automating workflows and processes, you may have encountered the frustrating “SQLite Full” error. This happens when your database becomes overloaded with unnecessary data, causing the system to stop functioning correctly.

In this blog post, we’ll walk through the error and show you how to delete data from the execution_entity and execution_data tables to free up space and keep n8n running smoothly.

What is the “SQLite Full” Error?

The SQLite Full error occurs when the SQLite database file exceeds its allocated space or when SQLite cannot write data due to a lack of disk space. In n8n, this can happen if there is an accumulation of old or unnecessary data, especially in tables that store execution logs and metadata like execution_entity and execution_data.

---

Locate n8n folder and change directory

cd ~/.n8n

SQLite is a file that will be datbase.sqlite now you can isntall sqlite3 to login database

sqlite3 database.sqlite

How to Identify the Problem

The first step in resolving this issue is identifying the tables that are taking up the most space. A quick way to do this is by checking the COUNT of records in different tables using SQL queries.

For example, you can run these queries in SQLite:

.tables

SELECT COUNT(*) FROM execution_data;
SELECT COUNT(*) FROM execution_entity;

In our case, we found that the execution_data table had over 7,600 records, and the execution_entity table contained almost 3,000 records.

Solution: Delete Old Data to Free Space

To resolve the “SQLite Full” error, you need to clean up the execution_data and execution_entity tables. These tables often hold logs of previous executions, which, if not pruned regularly, can take up a large amount of space.

Here’s how you can delete the data:

Delete Data from execution_entity Table: The execution_entity table holds metadata about the workflow executions. Deleting old records from this table will significantly reduce the size of your database.Run this query to delete the data

DELETE FROM execution_entity;

Delete Data from execution_data Table: The execution_data table stores the actual execution logs and results. Like the execution_entity table, this data can build up over time, consuming more and more space.To delete old records and free up space, run:sqlCopyDELETE FROM execution_data; This command clears out all the execution logs stored in the execution_data table.

DELETE FROM execution_data;

Vacuum the Database

After deleting the data, it’s a good practice to run the VACUUM command. This command optimizes the database and removes any unused space left behind by the deleted records.

To run the vacuum:

VACUUM;

This will compact the SQLite database and ensure that the space is reclaimed, reducing the file size further.

How to Prevent the Issue in the Future

While deleting data manually will solve the immediate problem, it’s important to prevent the database from getting bloated again. Here are a few tips:

Enable Execution Pruning in n8n: n8n has an execution pruning feature that automatically removes old data from the execution_data and execution_entity tables.

You can configure this in your .env file.For example, set these parameters:env

EXECUTIONS_PRUNE=true
EXECUTIONS_PRUNE_MAX_COUNT=1000
EXECUTIONS_PRUNE_TIMEOUT=7

Monitor Your Database Size Regularly: Periodically check the size of your SQLite database using the following command

ls -lh ~/.n8n/database.sqlite

This will give you a quick overview of the database file’s size, so you can take action before it gets too large.

Conclusion

The SQLite Full error in n8n can be a serious issue that disrupts your workflow automation. However, by deleting old data from the execution_data and execution_entity tables and using the VACUUM command, you can free up space and resolve the issue.

Additionally, configuring execution pruning will help prevent this issue from happening again in the future, ensuring your database remains optimized and your workflows continue to run smoothly.

If you’re experiencing any other issues or need help configuring n8n, feel free to reach out to us at SyncBricks. We’re here to help you optimize your automation workflows and keep your systems running efficiently!

---

About SyncBricks:

At SyncBricks, we specialize in helping businesses streamline their processes through intelligent automation. Our expertise in low-code platforms and AI solutions allows us to create efficient workflows that save time and resources, enabling you to focus on what matters most.

How to Self Host n8n