last updated: 2024-02-01
Introduction
There is huge knowledge available about SAP HANA reclaim / shrink / defragmentation topic, but the knowledge is very fragmented (Help Portal, Blog post, SAP KBA, SAP Note, other). In order to simply and make content consumption easier I have created a centralized page, a single source of the available information regarding SAP HANA persistence. Further I would like to help you with shrinking exercise in SAP HANA, express edition to demonstrate the process.
Table of Content (The internal links had been removed cause the new blogging platform only supports basic html, and the use of anchor links not supported.)
- Task 1. - Exercise
- Prerequisites
- Background
- Exercise
- Conclusion
- Task 2. - Documentation Library
- Where to find documentations in SAP Help Portal?
- Which is the master SAP Knowledge Base Article of the SAP HANA persistence?
- When (in general) and after what action, the fragmentation needs to be checked?
- Which other documentation is important in context of monitoring SAP HANA persistence?
- Which other documentation is important regarding SAP HANA reclaim / shrink / defragmentation topic?
- Which SAP HANA Alert IDs belongs to the topic?
- What are the available tools for defragmentation?
- What is the typical scenario after data deletion?
- What are the known issues?
- What blog posts can be helpful?
- Other articles in this series
- Other articles in connected series
- Useful bookmark
Task 1. – Exercise
Prerequisites
- Hypervisor (VMware Workstation Player 16)
- SAP HANA, express edition (db version: 2.00.061)
Install SAP HANA 2.0, express edition on a Preconfigured Virtual Machine (with SAP HANA XS Advanced) - SAP HANA Studio (version: 2.3.63)
- Frequently Asked Questions About SAP HANA®, Express Edition
Background
During the exercises I will use test database SFLIGHT. More information can be find:
SAP HANA, express edition and SFLIGHT demo database, modeling – Complete Tutorial
The SFLIGHT schema is in the Catalog folder.
Exercise
The actual fragmentation can be identified with the following SQL Statement: "HANA_Disks_Overview".
- 1969700 - SQL Statement Collection for SAP HANA
- 3293572 - SQL Statement Collection: "HANA_Disks_Overview" report for SAP HANA
In production system the fragmentation (~30%) is accepted, should not do reclaim. It will be defragmented to demonstrate the process (test system).
ALTER SYSTEM RECLAIM DATAVOLUME 120 DEFRAGMENT;
The following values (all) had been decreased: TOTAL_GB, UNUSED_GB, FRAG_PCT.
❗Best Practice: the RECLAIM process can be scheduled when the system load is minimal.
- 2400005 - FAQ: SAP HANA Persistence ➡️ 7. How can the persistence be defragmented?
"When RECLAIM is run in parallel to production load and modifications there is a certain risk of significant runtime overhead depending on the used SAP HANA Revision level:"
Conclusion
Free up space on DISK level is an easy task in case of SAP HANA. First, we need to identify if there is unused disk space available. When the unused space and the fragmentation details shows we can do the defragmentation. From SAP HANA 2.0 SPS06 this feature had been automated.
Task 2. – Documentation Library
IMPORTANT !
=================================================================
Where to find documentations in SAP Help Portal?
- SAP HANA Administration Guide for SAP HANA Platform – Persistent Data Storage in the SAP HANA Database
- SAP HANA SQL Reference Guide for SAP HANA Platform – ALTER SYSTEM RECLAIM DATAVOLUME Statement (System Management)
- SAP HANA Troubleshooting and Performance Analysis Guide - Reclaiming Disk Space
Which is the master SAP Knowledge Base Article of the SAP HANA persistence?
- 2400005 - FAQ: SAP HANA Persistence
=================================================================
When (in general) and after what action, the fragmentation needs to be checked?
- 2388483 - How-To: Data Management for Technical Tables
- 2400024 - How-To: SAP HANA Administration and Monitoring
- 2718597 - Collection solutions for some big growth tables relevant to HANA DB
- 3348393 - Overview of the data management process in the context of SAP HANA
❗Note: To check the data management activity from DISK perspective that is a good practice to run the "HANA_Disks_Overview" report before and after the activity. Compare the numbers especially the fragmentation. After the delete/archive activity the USED_GB will decrease, the UNUSED_GB will increase, the FRAG_PCT will also increase in general. Probably the fragmentation now shows significant number (higher than accepted value). These indicators shows that reclaim can be executed.
Which other documentation is important in context of monitoring SAP HANA persistence?
- 2988616 - /hana/shared volume full or growing
- 3054023 - Monitoring hana/data volume growth
- 3047379 - Monitoring hana/log volume consumption
- 3195436 - Analyze high disk utilization in HANA Database
Which other documentation is important regarding SAP HANA reclaim / shrink / defragmentation topic?
- 3194175 - How to reduce table disk size
- 3265639 - How to reduce History Table size
- 2910857 - Increase Data Volume free space
- 2499913 - How to shrink SAP HANA Data Volume size
- 3272698 - Data volume reclaim in High Availability setup
- 2984269 - How to cancel DATAVOLUME RECLAIM activity
- 2815538 - High disk consumption reported under OTHER_GB
- 2696420 - How to Manually Reclaim LOB space on SAP HANA
- 3235614 - HANA data volume size increased after table redistribution
- 3100336 - What is fragmentation in context of SAP HANA persistence?
- 3198269 - Enable Automatic Data Volume Reclaim in SAP HANA 2 SPS06
- 2937935 - High disk usage on SAP HANA Data volume caused by technical tables
- 2348397 - Shrink the Data Volume on Secondary Site with SAP HANA System Replication
Which SAP HANA Alert IDs belongs to the topic?
- 1870858 - HANA Alerts related to file system utilization
What are the available tools for defragmentation?
The general way to do defragmentation is to use SQL commands. The action can be done in the following way also.
- SAP HANA Administration with SAP HANA Cockpit - Reclaim Space
- 2222218 - FAQ: SAP HANA Database Server Management Console (hdbcons)
SAP HANA Academy video explains the reclaim process via SAP HANA Cockpit.
<VIDEO REMOVED from YOUTUBE because SAP HANA Academy channel stopped, video needs to be replaced with new URL when it is available>
What is the typical scenario after data deletion?
- 3209719 - Data file size is not reduced after data deletion.
- 3028886 - Table size on disk does not decrease after data archiving
- 3202583 - Disk size of the Audit Log table is huge even after clearing
- 2088183 - Memory usage not decreasing after the deletion of entries in a table
- 2731449 - After table deletion and successful Delta Merge, table size Is not decreased
What are the known issues?
- 2973560 - Data Volume Reclaim Finishes Early
- 2332284 - Data volume reclaim failed because of snapshot pages
- 3166382 - Reclaim database space, overhead parameter on SAP HANA
- 2910343 - SAP HANA data volume reclaim free up less space than expected
- 2539644 - Reclaim datavolume effected by data backup and System Replication SAP HANA
- 3202911 - ALTER SYSTEM RECLAIM DATAVOLUME DEFRAGMENT does not release all the expected space
What blog posts can be helpful?
- How to reduce your HANA database size by 30%
- How to Reclaim size of volume “/hana/log” when it is full.
- SAP Hana DB Disk Persistence –Shrink HANA Data volume
- How to reclaim fragmented space at OS level from HANA Data volume
- Did you know that running a reorganization or mass cleanup in HANA will increase data volume usage a...
Other articles in this series
✍️ Collected information about memory in context of SAP HANA
✍️ Collected information regarding partitioning in SAP HANA (with examples)
✍️ Collected information regarding High Availability and Disaster Recovery in SAP HANA
Other articles in connected series
✍️ Where can I find knowledge and information belongs to SAP HANA?
✍️ Where can I find information about the available tools for SAP HANA (all types of use)?
Useful bookmark
🔖 2872774 – Bookmark of Frequently Asked Questions for SAP HANA
🔖 3311408 – Bookmark of SQL Statement Collection reports for SAP HANA
🔖 2658020 – List of SAP HANA SQL Error Codes
Do you have further questions?
Q&A link for SAP HANA: https://answers.sap.com/tags/73554900100700000996
Contribution
If you find any missing information belongs to the topic, please let me know. I am happy to add the new content. My intention is to maintain the content continuously to keep the info up-to-date.
Release Information
| Release Date | Description |
| 2023.01.16 | First/initial Release of the SAP Blog Post documentation (Technical Article). |
| 2023.03.11 | Section added: Table of Content |
| 2024.02.01 | SAP Community 2024 optimized content. |
- SAP Managed Tags:
