# ERP Data Archiving Strategy: What to Archive and What to Purge

> Source: https://netray.co/erp-help/itm-erp-data-archiving-strategy-purge-old-transactions
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: ERP Help

## 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.

## Kind

how-to

## System

it-leadership

## Erp

ERP data management (vendor-neutral)

## Module

Archiving / Database growth

## Query

ERP data archiving strategy purge old transactions

## Alternate Queries

- 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

## 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.

## Steps

### Archiving workflow

**Items:**

- Measure first: list the largest tables and their growth rate over the past year. Archive what actually drives size and slowness, not what is easy.
- Classify each large table: transactional history, audit or log data, interface staging, attachments, or temporary and work tables.
- Set 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.
- Check 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.
- Decide the target: an archive database, a read-only data warehouse, or compressed storage with a query path. Confirm who will search it and how.
- Test on a refreshed non-production copy: run the archive, then run key reports, period-end processes and a restore.
- Run in small batches during a quiet window, with a verified backup and a record count reconciliation before and after.
- Schedule it. A one-time clean-up regrows; build recurring housekeeping and monitor growth.

## Sections

### Where the growth usually is

**Body:**

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.

**Code:**

-- 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

**Body:**

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

**Body:**

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

**Body:**

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.

## 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.

## Ai Angle

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.

## Faq

### How long must we keep ERP data?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

ERP data archiving, ERP database size reduction, ERP data retention policy, purge old ERP transactions, archive ERP history, ERP database growth, ERP housekeeping jobs, SAP data archiving, ERP audit log purge

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json