Automating SAP HANA Housekeeping with SAP HANA Cleaner (hanacleaner) & DB13 SQL Fallback: Complete Modern Runbook

1. Overview & Architecture of SAP HANA Cleaner #

SAP HANA databases continuously accumulate operational telemetry, backup metadata, traces, and alert logs. Without proactive housekeeping, enterprise production systems face severe stability bottlenecks:

┌─────────────────────────────────────────────────────────────────────────────┐
│                   SAP HANA UNMANAGED ACCUMULATION RISKS                     │
├───────────────────────────────┬─────────────────────────────────────────────┤
│ Backup Catalog (M_BACKUP_CAT) │ Hundreds of thousands of log backup records │
│                               │ → Massive catalog size & slow DB startup    │
├───────────────────────────────┼─────────────────────────────────────────────┤
│ Trace Directory (/trace)      │ Runaway *.trc, *.dump, core files           │
│                               │ → Filesystem 100% full & DB freeze          │
├───────────────────────────────┼─────────────────────────────────────────────┤
│ Statistics Service Alerts     │ _SYS_STATISTICS tables ballooning in RAM    │
│                               │ → High memory consumption in Nameserver     │
├───────────────────────────────┼─────────────────────────────────────────────┤
│ Audit Trail (_SYS_AUDIT)      │ Millions of unpruned security audit records │
│                               │ → Degraded query performance on views       │
└───────────────────────────────┴─────────────────────────────────────────────┘

SAP HANA Cleaner (hanacleaner.py) is the authoritative, open-source housekeeping engine developed by SAP Product Support (authored by Christian Elswede, github.com/chriselswede/hanacleaner and documented in SAP Note 2399996).

Prerequisite: Persistence Log Mode Verification #

Before implementing automated catalog housekeeping, verify that the database persistence log mode is set to normal. If set to overwrite, log backups are disabled and catalog maintenance behaves unpredictably:

-- Ensure standard persistence log mode across SYSTEMDB and tenants
ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM') 
  SET ('persistence', 'log_mode') = 'normal' WITH RECONFIGURE;

Architectural Cleanup Lifecycle #

                                  ┌───────────────────────────┐
                                  │      hanacleaner.py       │
                                  │ (Python 3.x / hanacleaner)│
                                  └─────────────┬─────────────┘
                                                │
                 ┌──────────────────────────────┼──────────────────────────────┐
                 │                              │                              │
                 ▼                              ▼                              ▼
    ┌───────────────────────────┐ ┌───────────────────────────┐ ┌───────────────────────────┐
    │     DATABASE ENGINE       │ │     FILESYSTEM ENGINE     │ │    STATISTICS & AUDIT     │
    ├───────────────────────────┤ ├───────────────────────────┤ ├───────────────────────────┤
    │ * Prune Backup Catalog    │ │ * Purge old *.trc files   │ │ * Prune _SYS_STATISTICS   │
    │ * Purge Data Backups      │ │ * Truncate large traces   │ │   alert histories         │
    │ * Remove physical backups │ │ * Remove *.dump / core    │ │ * Truncate _SYS_REPO and  │
    │   from disk (-bb true)    │ │ * Log file rotation       │ │   _SYS_AUDIT records      │
    └───────────────────────────┘ └───────────────────────────┘ └───────────────────────────┘

2. Dedicated Technical User & Security Privileges #

Never run automated housekeeping with generic superusers like SYSTEM. Create a dedicated technical user HANACLEANER, disable password lifetime expiration to prevent unexpected job failures after 90/180 days, and grant least-privilege authorizations:

-- 1. Create technical user with non-expiring password
CREATE USER HANACLEANER PASSWORD "<YourSecurePassword>" NO FORCE_FIRST_PASSWORD_CHANGE;
ALTER USER HANACLEANER DISABLE PASSWORD LIFETIME;

-- 2. Grant table-level permissions for statistics, lock, and repository housekeeping
GRANT SELECT, DELETE ON _SYS_STATISTICS.HOST_OBJECT_LOCK_STATISTICS_BASE TO HANACLEANER;
GRANT SELECT, DELETE ON _SYS_STATISTICS.STATISTICS_ALERTS_BASE TO HANACLEANER;
GRANT SELECT, DELETE ON _SYS_REPO.OBJECT_HISTORY TO HANACLEANER;
GRANT SELECT ON _SYS_STATISTICS.STATISTICS_EMAIL_PROCESSING TO HANACLEANER;

-- 3. Grant required administrative system privileges
GRANT AUDIT ADMIN, AUDIT OPERATOR, BACKUP ADMIN, CATALOG READ, LOG ADMIN, MONITOR ADMIN, RESOURCE ADMIN, TRACE ADMIN TO HANACLEANER;

Basis Best Practice: Execute the grants above in both the SYSTEMDB and the target Tenant Database(s) if you clean both containers.

3. Secure Connection Configuration (hdbuserstore) #

To prevent embedding plaintext passwords in cron jobs or bash scripts, store connection parameters in the SAP HANA Secure User Store (hdbuserstore).

In Multitenant Database Container (MDC) environments, utilize the Nameserver SQL port (3<instance_nr>13) combined with the @<TENANT> virtual routing identifier. This connects via the Nameserver and routes transparently to the tenant container without needing to query internal tenant SQL ports:

# Execute under <sid>adm:
su - <sid>adm

# Syntax:
# hdbuserstore set <KEY> <hostname>:3<nr>13@<TENANT_NAME> <USER> "<PASSWORD>"

# 1. Store connection key for Tenant Database (e.g. host 'hana-node01', instance 00, tenant 'PRD'):
hdbuserstore set HANACLEANER_<TENANT> <hostname>:3<nr>13@<TENANT> HANACLEANER "<YourPassword>"

# 2. Store connection key for SYSTEMDB (routes directly to SYSTEMDB container):
hdbuserstore set HANACLEANER_SYSTEMDB <hostname>:3<nr>13@SYSTEMDB SYSTEM "<YourSystemPassword>"

# 3. Verify that keys are registered properly:
hdbuserstore list

4. Production Execution Commands & Parameter Deep-Dive #

Standard Production Execution Syntax #

Run hanacleaner.py with tailored retention parameters for Tenant and SYSTEMDB containers:

# 1. Clean Tenant DB: Retain 7 days of backups, 180 days of traces, 360 days of alert history
python hanacleaner.py -k HANACLEANER_<TENANT> -be 7 -bb true -tc 180 -te 180 -tf 180 -ar 360

# 2. Clean SYSTEMDB: Retain 2 days of backups, 180 days of traces, 360 days of alert history
python hanacleaner.py -k HANACLEANER_SYSTEMDB -be 2 -bb true -tc 180 -te 180 -tf 180 -ar 360

High Availability / Cluster Execution (-vlh Parameter) #

If your SAP HANA database runs in a High Availability (HA) cluster (such as Linux Pacemaker, SUSE HAE, or Red Hat Enterprise Linux Cluster) where the database is bound to a Virtual Hostname or floating IP rather than the physical node hostname, pass the -vlh flag:

# Run with Virtual Local Host in HA Cluster environments:
python hanacleaner.py -k HANACLEANER_<TENANT> -be 7 -bb true -tc 180 -te 180 -tf 180 -ar 360 -vlh <virtual_hostname>

Important: Do NOT pass -vlh if running on a standard standalone host or if the database binds to the physical hostname. Only use -vlh when the OS hostname differs from the virtual hostname assigned to the active HANA instance.

Parameter Reference & Production Rationale #

5. Enterprise Automation via Linux Crontab #

Create a robust wrapper script /usr/sap/<SID>/home/scripts/run_hanacleaner.sh to ensure all SAP client environment variables are properly exported:

#!/bin/bash
# ==============================================================================
# Script: run_hanacleaner.sh
# Purpose: Automated housekeeping for SAP HANA SYSTEMDB and Tenant DBs
# ==============================================================================

# 1. Inherit SAP profile and client environment
if [ -f "$HOME/.profile" ]; then . "$HOME/.profile"; fi
if [ -f "$HOME/.sapenv.sh" ]; then . "$HOME/.sapenv.sh"; fi

# Define variables for this landscape
TENANT="<TENANT_NAME>"       # e.g., PRD, S4P, or HDB
VHOST="<virtual_hostname>"   # Set virtual hostname if HA cluster; leave empty "" if standalone
SCRIPT_DIR="/usr/sap/<SID>/home/scripts/hanacleaner"
LOG_DIR="/usr/sap/<SID>/home/scripts/logs"
DATE_STR=$(date +'%Y%m%d_%H%M%S')
LOG_FILE="${LOG_DIR}/hanacleaner_${DATE_STR}.log"

mkdir -p "$LOG_DIR"
echo "=== Starting SAP HANA Cleaner Housekeeping: $(date) ===" >> "$LOG_FILE"

# Determine optional -vlh flag
VLH_OPT=""
if [ -n "$VHOST" ]; then
    VLH_OPT="-vlh $VHOST"
fi

# 2. Cleanup Tenant Database
echo "--- Cleaning Tenant DB (${TENANT}) ---" >> "$LOG_FILE"
python "${SCRIPT_DIR}/hanacleaner.py" \
  -k "HANACLEANER_${TENANT}" \
  -be 7 -bb true -tc 180 -te 180 -tf 180 -ar 360 $VLH_OPT >> "$LOG_FILE" 2>&1

# 3. Cleanup SYSTEMDB
echo "--- Cleaning SYSTEMDB ---" >> "$LOG_FILE"
python "${SCRIPT_DIR}/hanacleaner.py" \
  -k "HANACLEANER_SYSTEMDB" \
  -be 2 -bb true -tc 180 -te 180 -tf 180 -ar 360 $VLH_OPT >> "$LOG_FILE" 2>&1

# 4. Prune hanacleaner execution logs older than 60 days
find "$LOG_DIR" -type f -name "hanacleaner_*.log" -mtime +60 -delete

echo "=== Completed SAP HANA Cleaner Housekeeping: $(date) ===" >> "$LOG_FILE"

Configure under <sid>adm crontab (crontab -e):

# Daily SAP HANA Housekeeping via hanacleaner at 02:30 AM
30 2 * * * /usr/sap/<SID>/home/scripts/run_hanacleaner.sh > /dev/null 2>&1

6. Alternative: Direct SQL Housekeeping via DB13 / SQL Console (No OS Access Required) #

In cloud-hosted, restricted, or strictly segregated enterprise environments, SAP Basis consultants frequently have database administrator access (DB13, DBACOCKPIT, SAP HANA Studio, SAP Cockpit, or hdbsql) but no SSH or OS access to the underlying Linux host.

In this scenario, backup catalog housekeeping can be scheduled directly as a recurring SQL command in DB13 (DBA Planning Calendar) or through an automated ABAP batch report using the dynamic SQL block below:

Dynamic Backup Catalog Pruning SQL Block #

DO
BEGIN
    DECLARE v_backup_id_str VARCHAR(20);
    DECLARE v_count INT;

    -- 1. Count the number of successful complete data backups currently recorded
    SELECT COUNT(*) INTO v_count
    FROM SYS.M_BACKUP_CATALOG
    WHERE entry_type_name = 'complete data backup'
      AND state_name = 'successful';

    -- 2. Retain the latest 7 complete data backups; prune older backups if count exceeds 7
    IF :v_count > 7 THEN

        -- Identify the 7th most recent backup_id to act as the retention cutoff
        SELECT TO_VARCHAR(backup_id) INTO v_backup_id_str
        FROM (
            SELECT backup_id,
                   ROW_NUMBER() OVER (ORDER BY sys_start_time DESC) AS rn
            FROM SYS.M_BACKUP_CATALOG
            WHERE entry_type_name = 'complete data backup'
              AND state_name = 'successful'
        )
        WHERE rn = 7;

        -- Delete all backup catalog entries (and physical backup files) strictly older than the cutoff
        EXEC 'BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID '
             || :v_backup_id_str || ' COMPLETE';

        -- Return execution feedback
        SELECT 'SUCCESS' AS STATUS,
               'Deleted all backups before Backup ID: ' || :v_backup_id_str AS MESSAGE,
               :v_count AS TOTAL_BEFORE
        FROM dummy;

    ELSE
        SELECT 'SKIPPED' AS STATUS,
               'Current count is ' || :v_count || '. No action required.' AS MESSAGE,
               :v_count AS TOTAL_BEFORE
        FROM dummy;
    END IF;
END;

How the DB13 SQL Script Operates: #

1. Safety Threshold Check: Queries SYS.M_BACKUP_CATALOG to count successful full data backups (entry_type_name = 'complete data backup' AND state_name = 'successful').
2. Dynamic Cutoff Determination: Uses the window function ROW_NUMBER() OVER (ORDER BY sys_start_time DESC) to locate the 7th newest successful backup ID.
3. Cascading Catalog & File Pruning: Executes BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID <id> COMPLETE.
   • The COMPLETE keyword instructs SAP HANA to also physically remove associated backup files from the disk if file-based backups are stored locally.
4. Non-Destructive Safeguard: If 7 or fewer full backups exist, the block safely branches to SKIPPED without deleting any records.
5. DB13 Integration: In transaction DB13, add a new action -> Select Execute SQL Command -> Paste the anonymous block -> Schedule daily or weekly.

7. Verification Queries & Health Validation #

Validate housekeeping results via SQL Console (ST04, DB02, DBACOCKPIT, or SAP Cockpit):

Check Catalog Volume & Oldest Active Backup #

SELECT 
    COUNT(*) AS total_catalog_entries,
    MIN(SYS_START_TIME) AS oldest_backup_entry,
    MAX(SYS_START_TIME) AS newest_backup_entry
FROM SYS.M_BACKUP_CATALOG;

Inspect Residual Alert History #

SELECT COUNT(*) AS active_alerts_count 
FROM _SYS_STATISTICS.STATISTICS_ALERTS_BASE;

Check Filesystem Reclaimed Space (via OS) #

df -h /hana/shared/<SID>/HDB<nr>/<host>/trace

8. Troubleshooting & Common Operational Gotchas #

┌──────────────────────────────────────┬─────────────────────────────────────────────────────────┐
│ SYMPTOM / ERROR                      │ ROOT CAUSE & BASIS RESOLUTION                           │
├──────────────────────────────────────┼─────────────────────────────────────────────────────────┤
│ "Cannot find userkey in              │ Cron executed without user profile environment.         │
│  hdbuserstore"                       │ Solution: Source ~/.profile or ~/.sapenv.sh in wrapper. │
├──────────────────────────────────────┼─────────────────────────────────────────────────────────┤
│ "Lock timeout on M_BACKUP_CATALOG"   │ Running cleanup while an active data backup is running. │
│                                      │ Solution: Stagger cron/DB13 job at least 2h from DB13.  │
├──────────────────────────────────────┼─────────────────────────────────────────────────────────┤
│ "Log segments not freeing"           │ Persistence log_mode is not set to normal.              │
│                                      │ Solution: Reconfigure log_mode = normal with RECONFIGURE│
├──────────────────────────────────────┼─────────────────────────────────────────────────────────┤
│ "Permission denied" during           │ Script executed under root or incorrect user.           │
│  trace deletion                      │ Solution: Must always execute strictly under <sid>adm.  │
├──────────────────────────────────────┼─────────────────────────────────────────────────────────┤
│ "Trace files not found with -vlh"    │ -vlh was passed on a non-cluster or wrong virtual host. │
│                                      │ Solution: Omit -vlh on standalone, or check cluster VIP.│
└──────────────────────────────────────┴─────────────────────────────────────────────────────────┘