Why ERPNext database cleanup needs a plan before any deletion
An operations lead notices that the ERPNext database and its nightly backups keep growing, while finance and stock teams still need years of history for audits, reconciliations and disputes. Cleanup can reduce some of that growth, but only when the team knows which data is disposable. The first decision is scope: which tables, log entries and document histories are safe to remove, and which must stay exactly as posted. Posted accounting entries, the stock ledger and the valuation data behind them are the records that must never be touched by a trim job, because they are the evidence for the company's books.
The data that usually grows without business value falls into a few groups. Orphan tables can remain after a DocType, custom field or app has been removed, and removed columns can leave data behind in the database. The Error Log and Scheduled Job Log grow with every failure or scheduled run, and *Version* and *Communication* records accumulate with every edit and email. Each group has different rules for retention, and some Frappe apps keep their own records. Before deleting anything, confirm with your Frappe or DeployOpen contact which tables belong to which installed app, and check the Frappe documentation for the version you run, because table and DocType behavior differs between releases.
Teams usually need this work in three situations: after removing an app or customization that left tables behind, after logs have grown for years without retention, or before a backup window starts to exceed the time available. Each trigger should lead to a written inventory and a rehearsal on a copy of the site, with a verified backup taken first. Expect the reduction to be different on every site, so ask for a measured baseline from your own environment rather than a size estimate. Plan the cleanup as a change with an owner, an approval and a rollback path.
What actually grows in an ERPNext database
A database that keeps growing usually holds more history than anyone decided to keep. Some of that history is safe to trim, some needs a retention rule, and some must stay for audit. Sort each component before you run a single delete, because the wrong cut can remove records your finance or compliance owners still need.
Transactional records
Invoices, journal entries, stock ledger rows, sales orders and payment entries form the core of the database and are usually the largest growth source. These records are the accounting and stock history, so they are not candidates for trimming. Plan for them to stay, and size backups and storage around them. Ask your finance owner whether any closed periods need an archive copy outside the live site, and confirm that any archive still meets your retention policy.
Child table rows
Line items on invoices, BOM items and similar child rows grow with every document, and they are stored in their own tables. They belong to a parent document, so removing them alone breaks that record. Treat them as part of the parent: any retention decision applies to the whole document, never to individual rows. Confirm with your ERP administrator which documents are eligible for removal before anyone writes a query against these tables.
Error Log and Scheduled Job Log
These two desk logs record failures and job runs, and they often grow without limit on a busy site. They are operational data rather than business records, so they are generally safe to trim after the problems they describe are resolved. Set a retention window that matches how far back your operations team investigates incidents. Keep the most recent entries for troubleshooting, and confirm with your security or compliance owner if any error entries are needed as evidence of an incident.
Version and Comment history
Version records capture field changes on documents, and Comment records hold notes and timeline entries. Both can grow quickly on heavily edited documents. Version history often supports audit trails for financial and access changes, so it needs a retention decision, not a blanket delete. Agree with your compliance owner which doctypes need full change history and for how long, and check the ERPNext documentation for the tracking settings that control what is recorded.
Email queue and communication records
The Email Queue and Communication doctype store sent mail, attachments and bodies. Queue rows that have already been sent or failed are operational and can usually be trimmed after the delivery window closes. Communication records tied to customers or suppliers may be part of the business record and should be kept. Confirm with your sales and support owners which messages must remain searchable, and remember that attachments carried by these records also drive backup size.
Leftover tables and columns
Uninstalled apps, deleted custom fields and abandoned customizations can leave orphan tables and unused columns behind. These are not always safe to remove, because another script, report or old backup may still reference them. Inventory them against installed apps and custom field definitions first, and keep a verified backup before any drop. Only remove a leftover structure after a dry run confirms that nothing in the current site reads or writes it.
Cleanup options and when each one fits
This table compares five ways to shrink an ERPNext database and backups, from dropping orphan tables to archiving closed fiscal years. Read each row for what it removes, where it can break a site, and what to confirm before running it on production. The main takeaway is that log and queue trimming carry the lowest risk, while removing business data or moving closed years out of the live site needs the most sign-off.
| Option | What it removes | Main risk | When it fits | What to confirm first |
|---|---|---|---|---|
| Dropping orphan tables | Database tables left behind by DocTypes or apps that no longer exist on the site | Removing a table that a custom report, script or leftover app still reads | The app that created the table has been uninstalled and no code references it | Search custom code and reports for the table name, and confirm the owning app is gone on every site in the bench |
| Removing columns from removed custom fields | Database columns for Custom Fields that were deleted in the desk | Losing data if the field was deleted, then re-added or still used by a script | Custom field was deleted long ago and its values are no longer needed for reporting or audit | Export the column values first, check print formats and server scripts for references, and confirm the change with the auditor if the field held financial data |
| Truncating or archiving log doctypes | Rows in Error Log, Scheduled Job Log, Activity Log and similar log records older than a set retention | Losing evidence needed for an incident or an access review | Logs are noisy, the retention policy allows deletion and no open investigation depends on them | Your written log retention policy, the Frappe setting that controls automatic log clearing for each doctype, and a backup taken before the purge |
| Purging old email queue and communication data | Sent or failed Email Queue records and older Communication records | Removing messages needed for dispute resolution or customer history | Email Queue backlog is large and the business does not need old outbound copies | Which communications count as records under your retention rules, and whether failed queue entries still need retry or investigation |
| Archiving closed fiscal years to a separate read-only site | Closed fiscal year data moved out of the live site, which stays available as a separate read-only copy | Reports, reconciliations or restatements may later need data that is no longer in the live site | Fiscal years are closed, audited and unlikely to change, and users accept a separate lookup path | Who can access the archive, how it is kept read-only, how it is backed up, and whether auditors accept the archived form as evidence |
How a safe cleanup runs, from inventory to verified restore
The sequence moves from an inventory of what the database holds to a verified, smaller database and backup, with a rollback point kept at every change. Each step closes with a check so the next one starts from evidence rather than assumption.
Inventory tables and DocTypes against installed apps
Start by listing the installed apps and their versions on the bench, then compare the database tables against the DocTypes each app defines. Mark tables that belong to no installed app, columns that no DocType or custom field references, and log tables that grow without limit. The engineer running the inventory records the output in a change ticket. It is done when every candidate has an owner, a source (app, custom field or log), and a reason for review. Confirm the table and column conventions with the Frappe version in use before relying on name patterns.
Take a full backup and prove it restores
Take a full site backup with the bench backup tooling for the version in use, including database and private and public files, and store a copy outside the server. Restore that backup onto a staging copy of the same version and apps, then open the site, log in, and check a sample of recent documents and a general ledger report. The step is done when the staging restore completes without errors and the sample checks match production. Confirm the exact backup flags and restore commands in the Frappe documentation for your release.
Run a dry run that lists candidates only
On the staging copy, run the cleanup logic in a mode that reports what would be dropped or deleted, without changing any data. Review the candidate list for tables that a custom script, report or integration still reads, and for log rows that a support or audit process needs. The engineer exports the dry-run output and attaches it to the change ticket. The step is done when the list reconciles with the inventory and every remaining question has a named owner.
Review candidates with business owners
Send the dry-run list to finance, operations and the teams that own integrations, with a plain statement of what each item is and what removing it means. Owners confirm which records must be kept, how long logs must be retained for their processes, and whether any data is needed for reporting or regulatory requests. Their sign-off is recorded in writing against each item. The step is done when every candidate is approved, kept, or deferred with a date set by the owner.
Apply changes in a maintenance window with a rollback point
Schedule the change in a maintenance window agreed with users, and stop scheduled jobs and integrations that write to the database. Take a fresh backup immediately before the change and label it as the rollback point. Apply only the approved candidates, in the order the dry run established, and keep a log of each command and its result. The step is done when the change log matches the approved list and the rollback point is confirmed to be readable.
Verify application health after the change
Restart the services, re-enable scheduled jobs and integrations, and test the paths that touch the changed tables: saving and submitting key documents, running the reports the business relies on, and checking the Error Log and Scheduled Job Log for new entries. Compare response times with the baseline taken before the change. The step is done when the test script passes and no new errors appear during an agreed observation period set by the operations owner.
Verify backup size and close the change
Take a new full backup after the change and compare its size with the pre-change backup, noting which reductions came from database content and which came from files. Test a restore of this new backup on staging to confirm it is complete. Record the results, the approved item list, the rollback point identifier and the retention rule for future logs in the change record. The step is done when the post-change backup restores successfully and the change record is handed to the operations team.
Setting log retention without losing audit evidence
Log retention in ERPNext is a policy decision first and a storage decision second. The Error Log and Scheduled Job Log doctypes in the desk grow with every failed request, every scheduled run and every background error, and they are rarely cleaned up unless someone deliberately sets a rule. Before you trim anything, decide what each log is for. Operational entries are useful for troubleshooting recent failures, while entries that show who ran a job, when a scheduled process executed or what failed during a period close may be evidence your auditors or internal reviewers later ask for. Those two purposes need different retention periods, and the cleanup job should reflect that split rather than deleting everything older than a single cutoff.
Start by mapping your records policy to these logs. Ask your compliance lead and external auditors which periods they expect you to produce, and whether error or job records count as supporting evidence for financial controls, access reviews or incident reviews. Confirm the retention requirements with them directly, since no general period can be assumed from the platform itself. Then decide which entries need to be exported or archived before any deletion runs: for example, a dated export of the relevant Error Log and Scheduled Job Log rows, stored in a system your records team controls, with a note of who approved it. Test the export with a dry run on a copy of the site so you know the file contains the fields your reviewers need, including timestamps, method names and user references.
Keep recent logs in the desk for day-to-day troubleshooting, and make sure the deletion is recorded somewhere outside the database you are cleaning. Log rotation for the server log files is a separate concern, covered in the operations group, so do not treat it as part of this retention decision. Document the agreed periods, the owner who signs off on changes, and the schedule for the cleanup, then review the policy when your records requirements change or when you upgrade ERPNext and the log structures shift.
Questions teams ask before cleaning up an ERPNext database
Can we delete old accounting data from ERPNext to shrink the database?
Generally, no. Posted ledger data such as General Ledger entries, Sales and Purchase Invoices and Payment Entries is part of your financial record, so it is normally kept even when it is old. Any change to posted accounting data needs sign-off from your finance lead and, where applicable, your external auditor before work starts. The cleanup that usually makes sense targets technical data instead: orphan tables left by removed apps, columns no longer in use, and log tables past their retention need. Ask the partner to classify every table and field in writing so finance can review the list before anything is touched.
How long does an ERPNext database cleanup take, and what drives the duration?
No fixed duration applies, because the work depends on the site. The main drivers are the size and number of log and orphan tables, how many sites share the database server, the volume of data to be archived or removed, how long your backup and restore cycle takes, and how many change windows your operations allow. A dry run on a restored copy of production is what turns an estimate into a plan. Ask for the timeline to be tied to the dry run results and to named milestones in the written scope, not to a general calendar window.
What does the cleanup need from our own team?
Your team needs to provide access to a recent backup or staging copy, a named business owner who can approve which data classes may be removed, and a finance or compliance reviewer for anything touching posted accounting records. Engineering needs a maintenance window, the ability to stop background jobs during the change, and someone who can confirm the application still works after each step. Ask the partner to list these inputs in the scope so the dependencies are agreed before the work starts.
Can our own team run the cleanup without an outside partner?
Yes, if your team has MariaDB and Frappe bench experience and a tested restore process. The risk comes from deleting the wrong rows or dropping a table that an app still reads, so a careful team runs every step on a restored copy first, records row counts and checksums, and keeps a rollback path. Whether to do it in-house or with a partner depends on how much certainty you need, how many sites and apps are involved, and how much internal capacity you have during the change window. Ask any partner to show how they would validate each step, whether or not you hire them.
How do backups change before and after a database cleanup?
Take a full, verified backup of the database and the files directory before any change, and keep it outside the server being cleaned. Confirm you can restore it into a separate environment, because a backup you have never restored is not a tested backup. After cleanup, take a new backup so the smaller database is the baseline, and review your retention settings, since backups made before the cleanup still contain the removed data. Check with your compliance team whether older backups must be kept or expired under your retention policy.
What happens if a cleanup breaks something in production?
The safe response is a pre-agreed rollback: restore the verified backup taken before the change, then re-run the application checks. A good written scope names the rollback owner, the decision point at which rollback is triggered, the checks used to confirm health, and how long the restore takes in your environment. Changes to posted accounting data are harder to reverse than removing logs, so those should be the most heavily tested. Ask how the partner records what changed so your team can audit the outcome.
How is the cost of an ERPNext database cleanup quoted?
DeployOpen does not publish a price list. A quote follows from a written scope that lists the tables and data classes in play, the number of sites and apps, the dry runs and backup tests included, the validation steps, and the change windows required. Most engagements are fixed-scope, so the scope, deliverables and timeline are agreed in writing before work starts. Ask for the quote to separate one-time analysis and cleanup from any recurring work, such as scheduled log retention jobs or ongoing monitoring under a retainer.
Security and compliance considerations for cleanup
Cleanup removes data, and removed data can still sit in backups, archives and logs. The cards below cover the access, retention and audit questions to settle with your compliance and security owners before the first dry run, not after the database has changed.
Access to backups containing personal data
Backups taken before a cleanup still hold the rows you meant to remove, including personal data in customer, supplier and employee records. Confirm who can restore those backups, where copies are stored, and how restores are logged. Restricting restore rights to named operators is often a simpler control than trying to scrub every backup set.
Deletion as a processing event
Deleting personal data is itself processing under most data protection regimes, so the cleanup needs a documented basis, a scope and an owner. Confirm which data subject requests and retention rules the deletion serves, and record what was removed, when and on whose instruction. Do not assume that a deleted row is a completed erasure while copies persist elsewhere.
Audit trails for financial records
Accounting entries, ledgers and tax-relevant documents often must be kept for a statutory period, and a cleanup that trims them can break your audit evidence. Confirm with your finance and external auditors which records are out of scope for deletion, and make sure the version history and Version DocType entries you rely on are not removed.
Restricting who runs cleanup scripts
Cleanup commands and database statements have effects that are hard to reverse, so they should run under a small set of named operators with change approval. Confirm who holds the database administrator credentials, whether cleanup runs are peer reviewed, and whether each run is recorded in your change system with its dry run output attached.
Server scripts during cleanup
Server Scripts run Python on document events or as API endpoints, and starting from version 15 they are disabled by default and must be enabled with the server_script_enabled setting. Enable them only for the window you need, record the change, and disable them again afterward. Confirm that no leftover script can fire on deletion or on a scheduled event.
Encrypting archives and dumps
Exports, pre-cleanup dumps and archived tables carry the same personal and financial data as the live database, often in plain files on shared storage. Confirm that archives are encrypted at rest, that keys are managed separately from the files, and that retention periods and deletion dates are set for each archive copy.
What smaller backups change in your recovery plan
A smaller backup only helps if your recovery objectives still hold, and the only way to know that is to restore it. Before any cleanup run, take a full backup and keep it for a defined retention period that your data owner, finance lead and auditors agree on. Do not delete that pre-cleanup backup until the post-cleanup state has been through a full business cycle and the team has confirmed nothing was lost. Record where it is stored, who can restore it and how it is encrypted, because a trimmed database restored from a backup that cannot be found does not help anyone.
Rehearse the restore on a staging copy that matches production in ERPNext and Frappe versions, installed apps and site configuration. Time the database restore, the file restore and the post-restore migrate and cache rebuild separately, since each one grows with the data you keep. Compare the measured figures with your recovery time objective and recovery point objective for the site. If the restore takes longer than the objective allows, the cleanup has not reduced your risk, and you should either change retention or change the objective. Ask your backup vendor or hosting provider to confirm how their tooling handles the site's database and private files, since they may not be backed up on the same schedule.
Update the runbook after every rehearsal, not once at project close. Name the exact commands, the backup set to use, the order of steps and the people who approve a go or no-go decision. Add a checkpoint that confirms the restored site opens, scheduled jobs resume and a sample of recent transactions and attachments is intact. Review the runbook whenever you change log retention, add a new cleanup job or upgrade ERPNext, because each of those changes can alter what a restore contains and how long it takes. Keep the rehearsal record with the change ticket so auditors can see that recovery was tested, not assumed.
