SAP HANA – reclaim LOB space

From time to time You check the size of the tables in HANA using e.g. a script: SQL:”HANA_Tables_LargestTables” from SAP note 1969700 SQL Statement Collection for SAP HANA. In the column LOB_DISK_GB, You observe a large amount of space is taken up by LOB data:

How to reclaim space

First You should use CHECK_TABLE_CONSISTENCY procedure can be used to check the consistency of the structure and data of tables. Run:

CALL CHECK_TABLE_CONSISTENCY('CHECK_HYBRID_LOB_OVERHEAD', '<SCHEMA_NAME>', '<TABLE_NAME>');

In my case, the command ended with the following message:

Lob container with CID= 0xfe164c323d has unusually high overhead, binToRawRatio=337.983 which is above the threshold of 10 with a total size of 377189761024. Please run REPAIR_HYBRID_LOB_OVERHEAD once on this table

So we repair:

CALL CHECK_TABLE_CONSISTENCY('REPAIR_HYBRID_LOB_OVERHEAD', '<SCHEMA_NAME>', '<TABLE_NAME>');

In HANA Studio -> Performance -> Threads You can check Your process:

When the process finished You see:

 Statement 'CALL CHECK_TABLE_CONSISTENCY('REPAIR_HYBRID_LOB_OVERHEAD','SCHEMA_NAME', ...' 
 successfully executed in 36:09.580 minutes  (server processing time: 36:09.553 minutes) 

When You once again call CHECK_HYBRID_LOB_OVERHEAD procedure everything should be OK- without error. Now You have to wait – in some time, HANA reclaim LOB space. In my system LOB usage dropped to 0.

Useful SAP Notes:

1977584 – Technical Consistency Checks for SAP HANA Databases
2696420 – How to Manually Reclaim LOB space on Hana
2950474 – DB table growth in SAP Hana related to Packed LOB’s