SAP Hana DB Disk Persistence –Shrink HANA Data volume

SAP Hana DB Disk Persistence –Shrink HANA Data volume

This blog is all about how to remove/ free up unused space inside an SAP HANA database disk persistence.

I would like to share my experience on how to perform the the Shrink of SAP HANA Data volume size which helps to free up the unused space inside an SAP HANA database disk persistence, reduce fragmentation on disk level and reclaim disk space.

The defragmentation happens online without blocking accesses to tables.

Prerequisites:

->Make sure we have latest successful backup of the SAP system.

->Before executing Disk Fragmentation we can see unused size of data file using the SQL statement (Hana_Disks_Overview), yoo can have latest SQL statements from the note 1969700 – SQL Statement Collection for SAP HANA

Syntax:

All this ALTER does is “Frees unused space inside an SAP HANA database disk persistence”.

ALTER SYSTEM RECLAIM DATAVOLUME [SPACE] [<host_port>]

<host_port>

Specifies the server on which the size of the persistence should be reduced.

<host_port> ::= ‘<hostname>:<port_number>’

> If <host_port> is omitted, then the statement is distributed to all servers with the persistence.

<<< OR >>>

The following statement defragments the persistence of all servers in the landscape and reduces them to 120% of the payload size.

ALTER SYSTEM RECLAIM DATAVOLUME 120 DEFRAGMENT

These instructions are as per SAP HANA SQL and System Views Reference. This statement reduces data volume size to a percentage of payload size. This statement works in a similar way to fragment of 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.

Before the shrink:

In my case the percentage of unused space is high,hence I considered the shrinking of the volume by executing below command which is specific to the server.

ALTER SYSTEM RECLAIM DATAVOLUME ‘<hostname>:<port no>’ 120 DEFRAGMENT

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.

The “120” indicates the fragmentation overhead that can remain, in this case 20 % of fragmentation on top of the existing 100 % data is acceptable. 120 is a reasonable value because due to temporary space requirements for table optimizations and garbage it is quite normal that 20 % of space is allocated and deallocated. Smaller values can significantly increase the defragmentation runtime and only provide limited benefit.

Result After the shrink:

Before Shrink UNUSED_SIZE was 453.67 GB and after shrink UNUSED_SIZE is 125.77 GB, it  RECLAIMED 328 GB

Troubleshooting:

->The progress of defragmentation can be monitored via SQL: “HANA_Disks_Data_SuperBlockStatistics” (SAP Note 1969700).

->The SAP HANA database trace (SAP Note 2380176) contains related messages as below:

Shrink DataVolume to <pct>% of payload size
Reclaim[0, ‘<datafile>.dat’]:<init>: sizes= (used= <used> mb / max=<max>mb | file= <file>mb), curOverhead= <pct>%,
maxOverhead= <targetpct>%, moving up to <mb>mb pages per cycle
DVolPart::truncate(payload= <payload>, maxSuperblockIndex= <ind>size= <source> –> <target>)
Reclaim[0, ‘<datafile>.dat’]:<truncate>: #<id>: truncated <mb>mb, sizes= (used= <mb>mb / max=<mb>mb |
file= <mb>mb), curOverhead= <pct>%, maxOverhead= <target_pct>%, <seconds>s)
Reclaim[0, ‘<datafile>.dat’]:<finished after <steps> steps>: moved <pages> pages (<mb>mb) in interval [0mb, 0mb[, sizes= (used=
<used>mb / max=<max>mb | file= <mb>mb), curOverhead= <pct>%, maxOverhead= <target_pct>%, <mb>mb truncated (<seconds>s)

->We can resume the RECLAIM task , so if it is terminated (e.g. due to “general error: Shrink canceled, probably because of snapshot pages”, SAP Note  1999880), it will continue next time at roughly the place where it stopped.

When RECLAIM is running in parallel to production load and modifications there is a certain risk of significant runtime overhead depending on the used SAP HANA Revision levels as below:

• SAP HANA 1.0: Rather high risk of runtime overhead
• SAP HANA 2.0 <= SPS 03: Reduced risk of runtime overhead
• SAP HANA 2.0 >= SPS 04: Further optimizations to reduce risk of runtime overhead

->We can also use the following SQL statement in order to find out which column store tables are occupying most of the space inside the volumes

select top 30 table_name, sum(physical_size) from M_TABLE_VIRTUAL_FILES
group by table_name
order by 2 desc

Reference:

->Note – 2499913 – How to shrink SAP HANA Data volume size

->Details:https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.01/en-US/20d1e5e57519101486c7812a5a8990e8.html