Below You find short instruction useful in the following cases:
- You want to free unused space inside an SAP HANA database disk persistence.
- You observe low percentage of Used/Total Size(%) of the Data Volume.
- The Total Size of the Data Volume is too much larger than the Used Size.
We are talking about this case:
The reason for this situation may be fragmentation. Generally shows up when the used space on disk is significantly smaller than before, because the allocated space won’t automatically reduce. For example due to archiving and deletion operations or cleanup after blocked garbage collection.
How to shrink data volume size
The following statement reduces data volume size to a percentage of payload size
ALTER SYSTEM RECLAIM DATAVOLUME [SPACE] [<host_port>] <percentage_of_payload_size> <shrink_mode>
This statement reduces data volume size to a percentage of payload size. This statement works in a similar way to defragmenting a hard drive. Pages that are scattered around a data volume are moved to the front of the volume, and the free space at the end of the data volume is truncated.
You can also try to reclaim the data volume by using hdbcons:
hdbcons 'dvol shrink -o <overhead_pct>'
Maybe You’ll need shrink the Data Volume on Secondary Site with SAP HANA System Replication. If yes, you have several options:
- Release unused disk space on secondary site by reconfiguring system replication and defragmenting the data volume.
- Release unused disk space on secondary by performing a takeover and defragmenting the data volume
- Release unused disk space on secondary by performing a full data shipment to secondary site.
More details You can find in this note: 2348397.
Sometimes data volume reclaim failed. I’ll write about it in the next article.
How to cancel datavolume reclaim activity
Execute below SQL instruction to find the CONNECTION_ID of the reclaim SQL:
SELECT C.CONNECTION_ID, PS.STATEMENT_STRING FROM M_CONNECTIONS C JOIN M_PREPARED_STATEMENTS PS ON C.CONNECTION_ID = PS.CONNECTION_ID AND C.CURRENT_STATEMENT_ID = PS.STATEMENT_ID WHERE C.CONNECTION_STATUS = 'RUNNING' AND C.CONNECTION_TYPE = 'Remote'
Use the CONNECTION_ID to cancel the running query
ALTER SYSTEM CANCEL SESSION <connection_id>