Data Maintenance and Cleanup: Retention Rules That Keep Commerce Fast

Every SAP Commerce installation is slowly filling itself with data nobody will ever read: finished cronjobs, ImpEx media, stored sessions, succeeded business processes, audit snapshots, saved-value histories. Left alone, this exhaust becomes millions of rows, degrades every query touching the affected tables, inflates backups and migrations, and eventually surfaces as "the site got slow and nobody knows why". The fix is unglamorous and completely mechanical: retention rules, configured once, reviewed occasionally.

This guide covers the full regimen: what the platform generates, the Data Retention Framework configuration for each data family, the one-time purge for systems that never cleaned up, and the custom-code habits that stop the problem at the source.

Two Frameworks, Use the New One#

The platform has two cleanup mechanisms. The legacy Maintenance Framework requires Java strategies per cleanup case. The Data Retention Framework does the same job with configuration: a FlexibleSearchRetentionRule selects doomed items with a query, a RetentionJob executes it in batches, a standard cronjob and trigger schedule it. Everything below uses the retention framework; the only time to touch the legacy framework is when you inherit code already built on it.

The pattern, once, since every rule below repeats it:

INSERT_UPDATE FlexibleSearchRetentionRule; code[unique=true]; searchQuery; retentionTimeSeconds; actionReference
# rule: what to delete and when

INSERT_UPDATE RetentionJob; code[unique=true]; retentionRule(code); batchSize
# job: executes the rule in batches

INSERT_UPDATE CronJob; code[unique=true]; job(code); sessionLanguage(isoCode)[default=en]
INSERT_UPDATE Trigger; cronJob(code)[unique=true]; cronExpression
# schedule

?CALC_RETIREMENT_TIME in the rule's query is provided by the framework: now minus retentionTimeSeconds.

Generic Audit: The Biggest Silent Grower#

Generic Audit (on by default since 6.6) snapshots every change to audited types, before and after images both. On a busy system with the default type list, audit tables grow faster than any business table. Two actions:

1. Audit only what a requirement names. Review the default list and disable types nobody will ever query audit history for. Products are the classic example:

audit.product.enabled=false

1. Kill auditing entirely where it has no purpose: local development and CI. Initialization and test runs get measurably faster:

auditing.enabled=false

For types you genuinely must audit, Change Log Filtering (since 1905) can conditionally include or exclude changes, cutting volume without losing the audit trail you actually need.

Technical Data: The Standard Retention Set#

Finished cronjobs#

ImpEx imports, catalog syncs, and Solr jobs each leave a cronjob instance behind. The rule below removes auto-generated, unscheduled jobs older than two weeks, regardless of result:

$twoWeeks = 1209600
INSERT_UPDATE FlexibleSearchRetentionRule; code[unique=true]; searchQuery; retentionTimeSeconds; actionReference
; cronjobCleanupRule; "select {c:pk}, {c:itemType}
   from {CronJob as c join ComposedType as t on {c:itemtype} = {t:pk}
        left join Trigger as trg on {trg:cronjob} = {c:pk} }
   where {trg:pk} is null
     and {c:code} like '00______%'
     and {t:code} in ( 'ImpExImportCronJob', 'CatalogVersionSyncCronJob', 'SolrIndexerCronJob' )
     and {c:endTime} < ?CALC_RETIREMENT_TIME"; $twoWeeks; basicRemoveCleanupAction

INSERT_UPDATE RetentionJob; code[unique=true]; retentionRule(code); batchSize
; cronjobRetentionJob; cronjobCleanupRule; 1000

INSERT_UPDATE CronJob; code[unique=true]; job(code); sessionLanguage(isoCode)[default=en]
; cronjobRetentionCronJob; cronjobRetentionJob;

INSERT_UPDATE Trigger; cronJob(code)[unique=true]; cronExpression
; cronjobRetentionCronJob; 0 0 0 * * ?

The three where-clause guards matter: no trigger means unscheduled (your nightly sync job is safe), the 00______% code pattern matches only auto-generated instances, and the type list keeps the rule from eating job types you did not consider.

Cronjob logs#

The platform does not delete old log files by itself; it ships the job but leaves scheduling to you:

INSERT_UPDATE CronJob; code[unique=true]; job(code); sessionLanguage(isoCode)[default=en]
; cronjobLogCleanupCronjob; cleanUpLogsJobPerformable;

INSERT_UPDATE Trigger; cronJob(code)[unique=true]; cronExpression
; cronjobLogCleanupCronjob; 0 0 0/1 * * ?

Hourly is the floor. If jobs run every few minutes, clean more often; a huge backlog of log files makes the cleanup itself slow.

Cronjob histories#

CronJobHistory rows track job progress and pile up fastest of all for frequent jobs, and their accumulation has direct, measurable impact on Backoffice and trigger performance. Since 2005 the platform ships a cleanup job for them out of the box. Verify it exists and is active in every environment; on the rare legacy patch level without it, SAP note 2848601 provides the setup ImpEx. This is the single most important entry in this guide: an inactive history cleanup is a slow-motion outage.

ImpEx media#

Every import and export creates ImpexMedia that outlives its job:

$twoWeeks = 1209600
INSERT_UPDATE FlexibleSearchRetentionRule; code[unique=true]; searchQuery; retentionTimeSeconds; actionReference
; impexMediaCleanupRule; "select {i:pk}, {i:itemtype}
   from {ImpexMedia as i}
   where {i:code} like '00______'
     and {i:modifiedTime} < ?CALC_RETIREMENT_TIME"; $twoWeeks; basicRemoveCleanupAction

INSERT_UPDATE RetentionJob; code[unique=true]; retentionRule(code); batchSize
; impexMediaCleanupJob; impexMediaCleanupRule; 1000

INSERT_UPDATE CronJob; code[unique=true]; job(code); sessionLanguage(isoCode)[default=en]
; impexMediaCleanupCronJob; impexMediaCleanupJob;

INSERT_UPDATE Trigger; cronJob(code)[unique=true]; cronExpression
; impexMediaCleanupCronJob; 0 0 0 * * ?

Keep its retention aligned with the cronjob rule so jobs and their media disappear together.

Saved values#

Backoffice's "Last Changes" panel is fed by SavedValues and SavedValueEntry rows, each also spawning rows in the props table. Cap the history at the property level:

# number of entries in "Last Changes"; the very latest change is kept even at 0
hmc.storing.modifiedvalues.size=0

Long-running systems that never set this are frequently sitting on millions of rows. Where direct database access exists (on-premise, or via SAP support on CCv2), the fast one-time purge, with the platform offline:

TRUNCATE TABLE savedvalues;
TRUNCATE TABLE savedvalueentry;
DELETE FROM props WHERE itemtypepk IN (
    SELECT pk FROM composedtypes
    WHERE internalcode = 'SavedValues'
       OR internalcode = 'SavedValueEntry'
);

Stored HTTP sessions#

Session failover persists sessions to the database, and stale ones degrade exactly the table your storefront hits constantly:

$oneDay = 86400
INSERT_UPDATE FlexibleSearchRetentionRule; code[unique=true]; searchQuery; retentionTimeSeconds; actionReference
; storedSessionRule; "select {s:pk}, {s:itemtype}
   from {StoredHttpSession as s}
   where {s:modifiedTime} < ?CALC_RETIREMENT_TIME"; $oneDay; basicRemoveCleanupAction

INSERT_UPDATE RetentionJob; code[unique=true]; retentionRule(code); batchSize
; storedSessionCleanupJob; storedSessionRule; 1000

INSERT_UPDATE CronJob; code[unique=true]; job(code); sessionLanguage(isoCode)[default=en]
; storedSessionCleanupCronJob; storedSessionCleanupJob;

INSERT_UPDATE Trigger; cronJob(code)[unique=true]; cronExpression
; storedSessionCleanupCronJob; 0 0/30 * * * ?

One day is conservative; high-traffic sites can and should go tighter.

Workflows and distributed ImpEx#

Workflow retention has no one-size answer because workflow lifetimes are business-specific. Answer three questions and encode them as rules: when is an unfinished workflow abandoned, how long must finished workflows survive for audit, and how long do their comments need to live? For projects using ImpEx distributed mode, add retention rules for DistributedImportProcess, ImportBatch, and ImportBatchContent; a heavy import regime generates startling volumes of all three.

Transactional Data#

Carts#

Anonymous carts are the highest-volume transactional type on most storefronts. The platform ships oldCartRemovalJob (in commercewebservices; the old ycommercewebservices variant is deprecated along with the rest of the OCC template extensions), but it only acts on sites you configure:

$siteUid = acmeSite
INSERT_UPDATE OldCartRemovalCronJob; code[unique=true]; job(code); sites(uid)
; oldCartRemovalCronJob; oldCartRemovalJob; $siteUid

Set different lifetimes for anonymous versus registered-customer carts (either through the job's parameters or a pair of retention rules): anonymous carts can die in days, while a logged-in customer's cart is a merchandising asset with a business-defined lifetime.

Business processes#

Password resets, order placement, fulfillment: each runs a BusinessProcess that stays in the database after finishing. Clean succeeded processes aggressively, keep failures longer for forensics:

$twoWeeks = 1209600
INSERT_UPDATE FlexibleSearchRetentionRule; code[unique=true]; searchQuery; retentionTimeSeconds; actionReference
; businessProcessRule; "SELECT {p:pk}, {p:itemtype}
   FROM {BusinessProcess AS p JOIN ProcessState AS s ON {p:state} = {s:pk} }
   WHERE {s:code} in ('SUCCEEDED')
     AND {p:modifiedTime} < ?CALC_RETIREMENT_TIME"; $twoWeeks; basicRemoveCleanupAction

INSERT_UPDATE RetentionJob; code[unique=true]; retentionRule(code); batchSize
; businessProcessCleanupJob; businessProcessRule; 1000

INSERT_UPDATE CronJob; code[unique=true]; job(code); sessionLanguage(isoCode)[default=en]
; businessProcessCleanupCronJob; businessProcessCleanupJob;

INSERT_UPDATE Trigger; cronJob(code)[unique=true]; cronExpression
; businessProcessCleanupCronJob; 0 0 0 * * ?

Add a second rule for FAILED and ERROR states with a longer retention. And if you customized process flows, clean their satellite data too: the OOTB example is EmailMessage, which auto-deletes only when successfully sent; failed sends accumulate until you deal with them.

Personal data retention#

Customer and order erasure (GDPR's right to be forgotten, and time-based retention) is covered by the platform's Personal Data Erasure jobs, with orderCleanupHooks and customerCleanupHooks as the extension points. The rule for custom development: any custom item type carrying personal data linked to a customer must register a cleanup hook, in the same pull request that introduces the type. Bolting on erasure later means auditing years of types under deadline. The data protection guide in this series covers the full framework.

One-Time Purge: Cleaning Up Years of Neglect#

Retention rules keep a clean system clean. A system that never had them, typically discovered during cloud migration planning when the database size quote arrives, needs a one-time purge first. Three tools, in order of preference:

1. Generated ImpEx removal scripts (the default choice): multi-threaded by default, no transaction-size limits, runs through the type system so interceptors and cascades apply.
2. Groovy in HAC: maximum flexibility, but scripts run in a single transaction by default (large deletes fail) and single-threaded unless you do the work.
3. Direct SQL: fastest and most dangerous; bypasses the type system entirely, so no delete interceptors, no validation, no cascading. Reserve it for types where you fully understand the table layout, like the SavedValues truncation above.

The ImpEx generation trick: an export script produces a removal script you then import. Reuse the retention-rule queries, replacing ?CALC_RETIREMENT_TIME with a literal date:

# assumption: no explicitly scheduled ImpEx jobs exist -> all ImpEx artifacts are disposable
REMOVE ImpexMedia; pk[unique=true]
"#% impex.exportItemsFlexibleSearch(""select {pk} from {ImpexMedia!}"");"

REMOVE ImpExImportCronJob; pk[unique=true]
"#% impex.exportItemsFlexibleSearch(""select {pk} from {ImpExImportCronJob!}"");"

# unscheduled, auto-generated catalog syncs
REMOVE CatalogVersionSyncCronJob; pk[unique=true]
"#% impex.exportItemsFlexibleSearch(""select {pk} from {CatalogVersionSyncCronJob! as cj
   left join trigger as t on {t:cronJob} = {cj:pk} }
   where {cj:code} like '0000____%' and {t:pk} is null"");"

# solr indexer jobs without triggers
REMOVE ServicelayerJob; pk[unique=true]
"#% impex.exportItemsFlexibleSearch(""select {pk},{code} from {ServicelayerJob! as j
   left join trigger as t on {t:job} = {j:pk} }
   where ({j:code} like 'solrIndexerJob_full_%' or {j:code} like 'solrIndexerJob_update_%')
   and {t:pk} is null"");"

# orphaned job logs
REMOVE JobLog; pk[unique=true]
"#% impex.exportItemsFlexibleSearch(""select {pk} from {JobLog} where {cronjob} is null"");"

Run the export in HAC (Console, ImpEx Export), then import the generated zip in Backoffice (System, Tools, Import) with "Allow code execution from within the file" checked, selecting importscript.impex inside the zip. Deletion errors on individual rows are ignorable; the point is bulk.

Type System Debris#

On-premise systems doing rolling updates accumulate old type system versions; only Default and the latest are needed, and the rest drop via HAC (Cleanup, Drop Type Systems) or:

ant droptypesystem -DtypeSystemName=USER_DEFINED_TYPE_SYSTEM

On CCv2 this is managed for you. What is not managed anywhere: columns left behind when attributes are removed from items.xml. A system update never drops them. Removing them is worthwhile before migrations and upgrades, and it is a rehearsed, non-production-first operation every time.

Do Not Hand-Roll This: sanecleanup#

Nearly everything in this guide, the rules, the jobs, the sensible defaults, exists as a ready-made extension: sap-commerce-tools/sanecleanup (Apache 2.0). Drop it into localextensions.xml, run an update, review what it configured against your project's specifics, and adjust retention periods where your compliance or business rules differ. Starting from sanecleanup and diffing beats transcribing ImpEx from any article, including this one.

The Custom-Code Habit That Prevents All of This#

Platform cleanup handles platform data. Your extensions create their own exhaust: temporary files, staging rows, integration payloads, custom process types. The habit that keeps the problem bounded is a one-line rule in the definition of done: anything your code creates with a bounded useful life ships with the retention rule that ends it. A feature that writes AcmeImportStagingRecord rows is not done until acmeImportStagingCleanupRule exists next to it.

Data cleanup is the least glamorous work on a commerce project and one of the highest-leverage: it is the difference between a system that performs the same in year five as in month one, and one that decays into a migration crisis. Set it up in sprint one, audit it quarterly, and it stays boring forever, which is the goal.