SAP HANA Housekeeping using HANACleaner

I try shortly show You how can You use HANAClenaer script to perform regular cleanup activities – both manually and from the scheduler.

HANAClenaer is Python script, which You can downloaded here.

When You downloaded script, put it in this localization:

/usr/sap/SID/HDBXX/exe/python_support
-- where XX is Your instance number
-- OR
-- when You log in OS to HANA system on sidadm try use command: cdpy

SAP HANACleaner can be used for the following cleanup tasks:

Task SAP Note
Cleanup of backup catalog entries2096851
Cleanup of backups1642148
Cleanup of trace files2380176
Cleanup of backup.log and backint.log1642148
Cleanup of audit logs2159014
Cleanup of SAP HANA alerts2147247
Cleanup of free log segments2083715
Cleanup of internal events2147247
Cleanup of multiple row store containers2222277
Cleanup of data file fragmentation1870858
Cleanup of SAP HANACleaner logs2399996
Cleanup of arbitrary directories (e.g. SAP HANASitter logs)2399979
Creation of optimizer statistics for virtual tables (e.g. SDA)1872652
Optimize compression of tables not compressed2112604
Optimize compression of tables with columns not compressed2112604
Optimize compression of tables with large UDIV overhead2112604
Cleanup of object lock history for ‘(unknown)’ objects1999998
Source: SAP Note: 2399996 

Help for this tool You can run in this way:

hostname>cdpy
hostname>./python hanacleaner.py --help

Before You will can use this tool add Your HANA user to hdbuserstore or other specific user. You can check entries hdbuserstore using below command:

-- log on sidadm
hostname>hdbuserstore LIST

To add Your user to store use below command:

hostname>hdbuserstore Set <KEY> <ENV>[@<DATABASE>] <USERNAME> <PASSWORD> 
-- where:
-- <KEY> -> entry key name
-- <ENV> -> database location (host:port)
-- <USERNAME> -> user name
-- <PASSWORD> -> password

When You added Your user to hdbuserstore, try use tool to run some housekeeping tasks. Below You can find most popular cleaning tasks:

-- cleaning up the backup catalog (backup catalog entries older than 21 days are deleted, the last 14 backups are left, print for output)
 python hanacleaner.py -k USERNAME -bd 21 -be 14 -br true
 
-- cleaning up the traces (older than 60 days deletes)
 python hanacleaner.py -k USERNAME -tc 60 -tf 60 -td true

-- delete alert log from the alert table (older than 14 days deletes, output true)
 python hanacleaner.py -k USERNAME -ar 14 -ad true

-- if the backup log file is larger than 50mb, compresses it
 python hanacleaner.py -k USERNAME -zb 50

-- reclaiming free log segment
 python hanacleaner.py -k USERNAME -lr 1

-- clear audit log database table (older than 100 dasy days clear)
 python hanacleaner.py -k USERNAME -ur 100

-- with this flags its possible to let handcleaner print out the housekeeping tasks without actually executing them (useful for debugging)
 python hanacleaner.py -k USERNAME -es false -os true -be 12 -bd 12 -tc 50 -ar 14 -lr 100

Sample output

hostname:sidadm> python hanacleaner.py -k USERNAME -ar 14 -ad true
 Will now check most used memory in the file systems. If it hangs there is an issue with  df -h, then see if the -fs flag helps.
 The most used filesystem is using
 75%
 HANACleaner executed 2021-03-26 22:31:48 with
 hanacleaner.py -k USERNAME -ar 14 -ad true
 
 2021-03-26 22:31:48
 hanacleaner by USERNAME on SID(XX)
 Cleanup Statements will be executed (-es is default true)
 
 (Cleaning of the backup catalog was not done since -be and -bd were both negative (or not specified)) (Cleaning traces was not done since -tc and -tf were both -1 (or not specified)) (Cleaning dumps was not done since -dr was -1 (or not specified)) (Cleaning of general files was not done since -gr was -1 (or not specified)) (Compression of the backup logs was not done since -zb was negative (or not specified))
 INFO: The flags -ao and -ad were changed to false since there are too many alerts for printout.
 12710 alerts were removed
     (Cleaning of unknown object locks entries was not done since -kr was negative (or not specified))
     (Cleaning of the object history was not done since -om was negative (or not specified))
     (Reclaim of free logsements was not done since -lr was negative (or not specified))
     (Cleaning of events was not done since -eh and -eu were negative (or not specified))
     (Cleaning audit logs was not done since -ur was -1 (or not specified))
     (Defragmentation was not done since -fl was negative (or not specified))
     (Reclaim of row store containers was not done since -rc was negative (or not specified))
     (Compression re-optimization was not done since at least one flag in each of the three compression flag groups was negative (or not specified))
     (Creation of optimization statistics for virtual tables was not done since -vs was false (or not specified))
     (Cleaning of the hanacleaner logs was not done since -or was negative (or not specified))
 hostname:sidadm>

If You need cleanup activities automatically, please schedule them in the cron.