ERP Data Archiving Strategy: What to Archive and What to Purge
ERP data archiving strategy purge old transactions
Also searched as
- ERP database growing too large what to archive
- how to archive old ERP data safely
- ERP data retention policy archive vs delete
- reduce ERP database size performance
Short answer
Find which tables drive database growth, set a retention rule per record type from legal and business needs, use the vendor's supported archive or purge routines where they exist, and prove you can still report on and restore the archived data before deleting anything from production.
Applies to: On-prem and self-hosted ERP databases (SQL Server, Oracle, DB2, PostgreSQL based). Supported archive tools vary by product; SaaS ERPs usually manage storage with the vendor.
Archiving workflow
- 1Measure first: list the largest tables and their growth rate over the past year. Archive what actually drives size and slowness, not what is easy.
- 2Classify each large table: transactional history, audit or log data, interface staging, attachments, or temporary and work tables.
- 3Set retention per class with finance, legal and operations. Tax, customs, quality and aerospace or defense records often carry multi-year retention rules, so confirm them rather than guess.
- 4Check what the vendor supports: standard archive, purge or housekeeping utilities, and what they cascade to. Avoid deleting directly from tables unless the vendor documents it.
- 5Decide the target: an archive database, a read-only data warehouse, or compressed storage with a query path. Confirm who will search it and how.
- 6Test on a refreshed non-production copy: run the archive, then run key reports, period-end processes and a restore.
- 7Run in small batches during a quiet window, with a verified backup and a record count reconciliation before and after.
- 8Schedule it. A one-time clean-up regrows; build recurring housekeeping and monitor growth.
Where the growth usually is
In most ERPs a small number of tables hold most of the volume. Typical culprits are inventory transaction history, general ledger and subledger detail, sales and purchase document lines, workflow and event logs, integration or message queues, scheduled job logs and attachments stored in the database.
Logs, staging tables and temporary data are the safest wins because they carry no financial meaning. Purging old interface and job logs often recovers a large share of space with no business impact, and should be done first.
-- SQL Server: largest tables by size SELECT TOP 20 t.name AS table_name, SUM(p.rows) AS row_count, SUM(a.total_pages) * 8 / 1024 AS total_mb FROM sys.tables t JOIN sys.indexes i ON t.object_id = i.object_id JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id JOIN sys.allocation_units a ON p.partition_id = a.container_id WHERE i.index_id <= 1 GROUP BY t.name ORDER BY total_mb DESC;
Archive versus purge versus leave alone
Archiving moves data out of production but keeps it retrievable. Purging deletes it. Leaving it alone is valid when the database is not actually slow and storage is cheap. Many performance problems come from missing indexes, stale statistics or poor queries, not volume, so check that before touching data.
Use archive for records you may need to produce for tax, quality traceability or litigation. Use purge for operational noise with no retention requirement. Be especially careful with anything linked to lot or serial traceability, warranty or regulated product history, where the chain of records must stay intact.
Keep reporting and audit trails working
The risk in archiving is not the move, it is what breaks afterwards. Reports that span multiple years, cost roll-ups, open-item aging and drill-downs from balances to source transactions can fail or show incorrect numbers if the underlying rows are gone.
Run your finance close, key operational reports and a few trace queries against the archived test copy and compare with the original. Also confirm how an auditor or customer request for old data will be answered, and who is authorised to run it.
Make it routine and safe
Archive in small date-based batches with referential integrity respected, and reconcile row counts before and after. Take a verified full backup beforehand and keep it for the period you agreed. Do not run archive jobs during close or heavy batch windows.
Document the rule, the owner, the retention period and the retrieval procedure. When the ERP is upgraded or migrated, include the archive in scope; otherwise it becomes an unreadable dump nobody can use.
Common pitfalls
- !Deleting directly from ERP tables with SQL when the vendor has a supported purge routine.
- !Archiving without confirming statutory retention periods.
- !Breaking lot or serial traceability by removing parts of the record chain.
- !No tested way to read the archive after an upgrade or when the original ERP is retired.
- !Expecting archiving to fix slowness that is really caused by indexes, statistics or a bad query.
- !A one-off clean-up with no scheduled housekeeping, so the database regrows in a year.
How an ERP-grounded AI assistant handles this
An AI assistant grounded in the ERP schema can help you size the problem and test the result: which tables grew most, how many records fall into each age band, and whether a given period's balances still reconcile after a test archive. ERPray queries read-only and shows the SQL, so DBAs can review it. It does not decide your retention periods or delete production data.
Frequently asked questions
How long must we keep ERP data?
It depends on the record type and your jurisdiction and industry. Financial and tax records commonly have multi-year requirements, and quality or traceability records in regulated sectors can be longer. Confirm with finance and legal rather than relying on a generic number.
Will archiving make the ERP faster?
Sometimes. It helps when large tables are scanned by reports or batch jobs and the data is not needed day to day. If slowness comes from poor indexing, out-of-date statistics or inefficient queries, tuning those usually delivers more with less risk.
Should we archive inside the ERP or to a separate store?
Use the vendor's supported mechanism where one exists, because it understands relationships between tables. A separate read-only store or warehouse is useful for long-term reporting, but only if you can still reconcile it and the format stays readable.
What about attachments and documents?
Attachments stored in the database can be a major share of size. Moving them to file or object storage with links retained is often a safer win than deleting transactional history, provided access control and backups cover the new location.
Related
How to Monitor ERP Performance: What to Track and Alert On
Monitor the ERP from the user's side first (response time of a few key transactions), then the layers beneath: database waits, blocking and long queries, application server health, batch and integration job duration, queues and storage. Baseline normal for each, alert on deviation, and keep history so you can answer slow-yesterday questions.
How-toHow to Test ERP Backup and Disaster Recovery
A backup is only proven when you have restored it and a user has transacted in the restored system. Define RTO and RPO with the business, restore the full ERP stack (database, application, integrations, attachments, secrets) to an isolated environment, run a scripted smoke test, time every step, and record gaps as action items.
CIO briefingData Platform for ERP Data: Replica, Warehouse or Lakehouse
Start with the smallest thing that removes reporting load from the ERP: a read replica or vendor replication feed. Add a warehouse or lakehouse when you must join ERP data with other sources, keep history, or serve many consumers. Put a governed semantic layer on top, because raw ERP tables are not a usable analytics or AI interface on their own.
CIO briefingERP Cloud Migration: The Risks a CTO Should Score First
The biggest risks are not infrastructure. They are customisations that do not survive, integrations that assume the old network, data quality and volume, a cutover window the business cannot accept, licensing changes, and loss of control over upgrade timing. Score each, and decide between lift and shift, hosted private cloud or SaaS re-implementation on that evidence.
How-toHow to Run an ERP User Access Review (Quarterly Checklist)
An ERP user access review is a recurring control where system owners confirm that every active user, role and privileged permission is still justified. Extract users and roles from the ERP itself, join them to HR status, send each business owner a scoped list to certify or revoke, record the decisions, and execute the removals with ticket evidence.
How-toERP Patching Cadence: How Often Should You Patch?
Patch the ERP stack in layers on different clocks: operating system and database monthly or quarterly, ERP vendor cumulative updates quarterly or semi-annually, and security fixes on an emergency path measured in days. Test every patch in a sandbox refreshed from production, with a scripted regression and a tested rollback.
AI for ERPAI for Infor SyteLine and CloudSuite Industrial
Add grounded AI to Infor SyteLine or CloudSuite Industrial: natural-language answers, agents over IDOs and ION, on-prem or CloudSuite deployment.
AI for ERPAI for SAP S/4HANA, Running On-Prem or in Your Private Cloud
Run AI on SAP S/4HANA without sending ERP data to a public API. On-prem and private-cloud architecture, CDS views, OData, and honest deployment trade-offs.
Stuck on ERP data management (vendor-neutral)?
Talk to engineers who work inside ERP data management (vendor-neutral) every week, and who build private AI that answers these questions from your own ERP data.