Space not reclaimed after table drop

[former_member605268]

Hello.

Have HDB 2.0.37.05 database (1.2TB in size) and one table with 902GB in size according to M_TABLE_PERSISTENCE_STATISTICS. This is MES_DB_AGGREGATE from SOLMAN database. We restore this database on test sandbox and try to DROP TABLE and next ALTER SYSTEM RECLAIM DATAVOLUME 120 DEFRAGMENT, but datavolume size not changed.

Also, what is strange if I make a copy of this table (CTAS) , then it takes up the size of 541 megabytes only.

What else can be done to reduce the size of the datavolume and /hana/data filesystem

Best regards,

Dmitry

[m__krijt]

I created a SAP Incident for the same, we are on revison 36.
SAP answered with: https://launchpad.support.sap.com/#/notes/2910004

Our system isn't upgraded yet, now I am doing the next task, which will take around 10 minutes with 1.000.000 records):

ABAP --> Stop jobs

User SAP<SCHEMA>
SQL> RENAME TABLE "MES_DB_AGGREGATE" TO "MES_DB_AGGREGATE_ORIG"
SQL> RENAME INDEX "MES_DB_AGGREGATE~0" TO "MES_DB_AGGREGATE~0_ORIG"
SQL> CREATE TABLE "MES_DB_AGGREGATE" LIKE "MES_DB_AGGREGATE_ORIG" WITH DATA
SQL> select * from indexes where table_name='MES_DB_AGGREGATE'
<automatic_created_index>
SQL> RENAME INDEX "<automatic_created_index>" TO "MES_DB_AGGREGATE~0"
SQL> DROP TABLE "MES_DB_AGGREGATE_ORIG"

ABAP --> Start jobs

SQL> ALTER SYSTEM RECLAIM DATAVOLUME 110 DEFRAGMENT

Kind Regards Marco Krijt

[deepak-kalra]

Hi kds63,

There are many reason for table "mes_db_aggregate" to grow large.

Please have a look at SAP note - 2345041 - How to Reduce and Maintain Table Growth in Technical Monitoring for Solution Manger 7.1 & 7.2

Link: https://launchpad.support.sap.com/#/notes/0002345041

I hope it helps.

Regards
Deepak