Quick Data Cleanup in SAP Commerce

[olga-strijewski]

Introduction

SAP Commerce generates operational data such as audit logs, job logs from cronjobs, job history, and more during regular use. Over time, this data can build up, consuming disk space and impacting performance. While periodic cronjobs are recommended for ongoing cleanup, sometimes a quick, one-time cleanup is necessary — for example, before a migration or to free up disk space. In this article, we’ll show you how to clean up this data efficiently using tools like Impex statements, Groovy scripts, and SQL commands.

Orphaned Types and Orphaned Media Files

Orphaned Types take up space in the database, and Orphaned Media Files can take up storage space on disk. Both can be easily cleaned up by going to HAC > Maintenance > Cleanup.

For a detailed guide on safely cleaning up orphaned objects, including how to handle them in CCv2, check out my other article - Orphaned Objects in SAP Commerce.

Media Items

Some types of media items in SAP Commerce become unnecessary over time and can safely be removed to free up system resources. A key benefit of SAP Commerce is that when a media item is deleted—whether through Backoffice, an Impex REMOVE statement, or a Groovy script — the associated physical file is also automatically deleted. This holds true for both on-premise setups and CCv2, where media files are stored in Azure Blob Storage.

However, if media records are deleted directly via SQL commands (not recommended), the underlying files will not be removed, resulting in orphaned files. If this happens, you can clean up these orphaned files using the “Delete Orphaned Media Files” procedure described in the previous chapter.

Two specific types of media items that often accumulate over time are cronjob logs and Impex media files, both of which can be cleaned up efficiently to save space.

Cronjob Log Files

SAP Commerce stores cronjob log files as media items with associated physical files. These logs are automatically deleted when their parent cronjob is removed. However, batch-deleting them separately in one operation can optimize the cleanup process and free up disk space faster.

Impex Media

Impex media are the files attached to Impex cronjobs, typically containing the scripts executed during the job. Unlike cronjob logs:

• Impex media are not automatically deleted when their parent cronjob is removed.
• They do not reference their parent cronjobs directly, so they remain in the system indefinitely, often accumulating in large numbers.

Impex media files are only used in Impex cronjobs. While they may have references in other types, those also ultimately lead back to Impex cronjobs. Therefore, it is generally safe to delete Impex media files, as most Impex cronjobs are completed, one-time processes. However, if you have recurring Impex cronjobs, you should carefully assess whether these files need to be retained.

Deleting Media Items

Timing the Cleanup

When performing a cleanup, it’s best to ensure the system is operating normally with no active issues or investigations. Cronjob logs and Impex media can be critical for troubleshooting, so avoid deleting them if you’re in the middle of resolving a problem. Also, schedule the cleanup during periods of low system load. For B2B systems, this often means weekday evenings or weekends. For B2C systems, you may need to run the cleanup late at night, considering your customers’ time zones to minimize disruptions.

Cleanup in Batch Mode

Deleting unnecessary media items, such as cronjob log files and impex media, is best done in batch mode. This allows the system to handle cleanup in a single operation, which is often faster and more efficient than removing items one by one. Please note that for this reason, we can't leave the "recent" items in the system and delete all the old ones, because then we won't be able to use the impex one-liner script below and will resort to removing items one-by-one, which will take longer and will not be as "quick" as this article promises.

To assess how many cronjob log files currently exist, use this query:

SELECT COUNT(*) FROM {LogFile}

To delete all cronjob log files, use the following script:

REMOVE LogFile[batchmode=true];itemtype(code)[unique=true]
;LogFile

Once you launch the deletion of all job logs, SAP Commerce will create an ImpEx cronjob to handle the operation. You can monitor the progress of the deletion through this cronjob - when the deletion starts, you’ll see a log entry similar to the following:

Starting import synchronous using cronjob with code=00008MLZ
Starting multi-threaded ImpEx cronjob "ImpEx-Import" (8 threads)

The cronjob code, in this example 00008MLZ, will be unique to your system.

Open Backoffice, search for the cronjob using its code (e.g., 00008MLZ), and monitor its progress. Once the cronjob status shows as “COMPLETED”, the deletion has succeeded. To verify, rerun your initial query to count the number of JobLog entries:

SELECT COUNT(*) FROM {LogFile}

If the deletion was successful, the result should be 0.

Please note that depending on the number of cronjob log files in your system, the process can take hours or even days - so be prepared for that. This will still be faster than removing these items with a cronjob one by one. From time to time, you can check how many items are removed thus far, by doing the "SELECT COUNT(*)" from the type you are cleaning up.

For Impex media, follow the exact same procedure as for cronjob logs, replacing LogFile with ImpExMedia in the querie and the script above.

Cleanup with a Groovy Script

When you need to clean up old items while keeping recent ones, you can use a Groovy script. The script processes deletions in batches, logs progress to both the console and system logs, and supports flexible date cutoffs. Logging is useful since the script may run for a long time, and your HAC session might time out. Logs will let you monitor progress even if your session disconnects.

You can find the script in the hybris-utils repository on GitHub. The cleanup process in the script is not limited to cronjob log files, but can be applied to any type or obsolete items in SAP Commerce where such items no longer serve a purpose and are safe to delete.

Sample logs:

Generated Query for LogFile (cutoff = 1Y 6M): SELECT {PK} FROM {LogFile} WHERE {creationtime} <= '2022-06-01 14:52:13'
Total LogFile items to delete (cutoff: 2022-06-01 14:52:13): 4,000
Deleted 1,000 LogFile items in this batch. Remaining: 3,000 (25.00% completed). Estimated time to complete: 15 minutes (or at 03:45 PM EST).
Deleted 1,000 LogFile items in this batch. Remaining: 2,000 (50.00% completed). Estimated time to complete: 10 minutes (or at 03:40 PM EST).
Deleted 1,000 LogFile items in this batch. Remaining: 1,000 (75.00% completed). Estimated time to complete: 5 minutes (or at 03:35 PM EST).
Deleted 1,000 LogFile items in this batch. Remaining: 0 (100.00% completed). Cleanup completed: 4,000 LogFile items deleted.
Obsolete items deletion script completed at: 03:30 PM EST
Total execution time: 20 minutes

For detailed usage and instructions, visit Hybris Obsolete Items Cleanup.

Stopping and Resuming the Cleanup

If the cleanup process is taking too long and needs to be paused, you have a couple of options depending on how the cleanup is running:

1. For a Cronjob:
   

   You can set the corresponding cronjob status to “ABORTED” and then restart the servers. After the restart, the cronjob will no longer be running but will have completed part of the cleanup. When you’re ready to continue, you can restart the same cronjob to pick up where it left off.

2. For a Groovy Script:
   

   To abort a Groovy script that’s running a cleanup operation, simply restart the server. The script will terminate immediately, though any changes already completed (e.g., deleted records) will remain applied. You can launch the script at a later time to resume from where it left off.

Cronjobs

Some cronjobs in the system are essential and must remain intact—this includes scheduled cronjobs with triggers. These should never be deleted. However, many completed cronjobs, such as those created during ad-hoc operations like ImpEx imports (e.g., via HAC) or catalog synchronizations, can usually be deleted once they are no longer needed.

In most systems, a regular maintenance procedure might retain a week or a month’s worth of these cronjobs to allow for potential investigations. However, since we’re performing a quick cleanup during a time when no investigations are ongoing, we can safely delete all cronjobs that are no longer required.

Step 1: Identify Cronjob Types

To determine which types of cronjobs exist and how many of each type are in the system, run the following query:

SELECT 
    {job.code} AS "Job Type", 
    COUNT({cronjob.pk}) AS "Number of CronJobs", 
    MAX({cronjob.modifiedTime}) AS "Latest Execution Time"
FROM 
    {CronJob AS cronjob 
     JOIN Job AS job 
     ON {cronjob.job} = {job.pk}} 
WHERE 
    NOT EXISTS (
        {{ SELECT 1 FROM {Trigger AS trigger} WHERE {trigger.cronJob} = {cronjob.pk} }}
    ) 
GROUP BY 
    {job.code} 
ORDER BY 
    COUNT({cronjob.pk}) DESC

This query will provide a table similar to the following:

Job Type	Number of CronJobs	Latest Execution Time
ImpEx-Export	154	2024-11-30 18:45:00
solrIndexerHotUpdateJob	47	2024-11-30 15:20:00
CatalogVersionSyncJob	32	2024-11-29 09:10:00

Step 2: Delete Specific Cronjob Types

If you decide to delete all cronjobs of a specific type, such as "ImpEx-Export", use the following Impex statement:

REMOVE CronJob[batchmode=true];job(code)[unique=true]
;ImpEx-Export

Monitoring the Deletion Process

Deleting cronjobs can take some time because, in addition to the cronjob itself, the process also removes associated items, such as:

• Job Histories

• Job Logs
• Log Files (including cleaning up related files from disk and database)

To make the process faster, it is strongly recommended to delete cronjob log files before deleting the cronjobs themselves. This reduces the cleanup burden during the cronjob deletion process.

If you want to monitor the progress of the deletion, you can use a SELECT COUNT(*) query to check how many cronjobs of the specified type are still in the system. Here’s the query for ImpEx-Export:

SELECT COUNT(*)
FROM {CronJob AS cronjob
      JOIN Job AS job
      ON {cronjob.job} = {job.pk}}
WHERE {job.code} = 'ImpEx-Export'

Run this query periodically to see how many cronjobs remain, until the count reaches 0.

Business Processes

We deal with business processes in a similar way to how we handled cronjobs.

Step 1: Identify Business Process Types

Issue the following FlexibleSearch query to determine the number of business processes by type:

SELECT 
    {processDefinitionName} AS "Process Type", 
    COUNT({pk}) AS "Number of Processes", 
    MAX({modifiedTime}) AS "Latest Update Time"
FROM 
    {BusinessProcess}
GROUP BY 
    {processDefinitionName}
ORDER BY 
    COUNT({pk}) DESC

This query will return a table similar to the following:

Process Type	Number of Processes	Latest Update Time
order-process	1023	2024-11-30 18:45:00
quote-process	245	2024-11-29 14:10:00
subscription-process	87	2024-11-28 09:10:00

Step 2: Delete Specific Business Processes

Once you’ve identified the types of business processes you want to delete, use the following Impex statement to remove all instances of a specific type, such as "order-process":

REMOVE BusinessProcess[batchmode=true];processDefinitionName[unique=true]
;order-process

This statement deletes all business processes of type "order-process" in batch mode for efficiency.

Step 3: Monitor the Deletion Process

To track the progress of the deletion, you can use a count query to check how many business processes of the specified type remain:

SELECT COUNT(*)
FROM {BusinessProcess}
WHERE {processDefinitionName} = 'order-process'

Run this query periodically to verify that the count decreases as the deletion progresses. The process is complete when the count reaches 0.

Tables that can be truncated

Certain tables in SAP Commerce, such as Solr index operation table, stored HTTP sessions, and audit data tables, can be truncated directly in the database. Truncation is the fastest method for deleting data and is ideal when the rows are not referenced elsewhere in the system.

When to Use Truncation

• Safe to truncate: Tables that are standalone and not referenced by other items, such as:
  • Solr index operation table (table SolrIndexOperation)
  • Stored HTTP sessions (table StoredHttpSessions)
  • Audit data tables (if audit data is not needed or has been archived) (tables ending with some number + "sn")
• Do not truncate: Tables that have dependencies, such as:
  • Media items (which reference underlying files)
  • Cronjobs and business processes (referenced by orders, workflows, etc.)

Important Notes on Audit Data

Audit data often occupies a significant portion of database space — potentially up to 90% if enabled for a long time. Before truncating audit tables:

1. Turn off audit data generation to prevent new data from accumulating immediately after the cleanup.
2. Refer to my article on audit data for guidance on identifying audit tables: Audit Data and Audit Logging in SAP Commerce.

For example, to truncate the solr index operation table:

TRUNCATE TABLE SolrIndexOperation;

Steps for Truncating Tables

1. Perform a backup:

   • Take a full database and media backup before truncating any tables.

   • Ensure you execute truncation during low-load times for minimal impact.

2. Identify the target table:
   • For audit tables, refer to the linked article above.
   • For non-audit tables, check the items.xml file. The deployment element specifies the corresponding database table for the item.
3. Execute the truncation:
   • Use the following SQL command in your database: TRUNCATE TABLE <table_name>;
     
   • Note: If you are hosted in CCv2, then you can issue a ticket to SAP so their DBA does the truncation for you.
4. Clear caches:

   • Use HAC to clear the cache, or

   • Restart Hybris for a complete refresh.

Why Truncate?

Truncation is significantly faster than traditional deletion because it bypasses row-by-row checks and constraints, provided the table is standalone and not referenced by other entities. However, this method must be used carefully, as truncating a referenced table can break referential integrity and lead to system instability or errors.

File System Cleanup

File system cleanup is an important step to reclaim disk space during a quick cleanup. Focus on removing unnecessary files, such as Hybris logs, old hot folder items, and unused Azure storage files in CCv2.

1. Hybris Logs:
   • Delete any old log files generated by SAP Commerce.
   • Consider automating this process by setting up a scheduled job to clean up logs older than a certain date.
2. Hot Folders:
   • Clear out old files in hot folders that are no longer being processed or required.
   • For recurring processes, establish an automated cleanup routine to avoid manual intervention.
3. Azure Storage (CCv2):
   • If your system is hosted in CCv2, review and clean up unused or outdated files in Azure Storage in Cloud Hot Folders.
   • Use Azure Storage Explorer or automated scripts to identify and delete unnecessary files.

Best Practices for File System Cleanup

• Plan Cleanup During Low-Load Times:
  • Perform file system maintenance during off-peak hours to minimize the impact on active operations.
• Backup Before Deleting:
  • Ensure critical files are backed up before deletion, particularly from hot folders or shared directories.
• Automate the Process:
  • Use cron jobs, scheduled tasks, or scripts to automate routine cleanups.
• Coordinate With Stakeholders:
  • Inform relevant teams before cleaning shared directories like hot folders, as other processes may depend on these files.

Conclusion

In this article, we outlined practical steps for quickly reclaiming disk and database space in SAP Commerce. By cleaning up obsolete database items, truncating non-referenced tables, and addressing file system clutter, you can optimize system performance and reduce storage costs. Regular maintenance and strategic cleanup ensure a more efficient and sustainable system over time.