HANA SLT - unclear data volume growth

[jtretina]

Hello,

I am facing quite huge data volume growth in one HANA system which is only used in SLT scenario (replications from ECC and HR systems). This growth started about month ago when HANA had just about 40GB size, from that point every day there are additional (uncleared) 30GB of data. Now the database has almost 900GB (only payload = file backup size) and so far I haven’t been successful with finding the root cause of this growth.

I have checked the size of all tables in disk, the biggest one is 13GB big, SUM is about 50GB. Current memory utilization is about 200GB. Can somebody navigate me to some view or table where could I check data volume utilization or how to explain this growth?

Thanks in advance!

Br, Jan

[Saritha_K]

Hi Jan,

You can refer to guide sap_hana_sql_and_system_views_reference_en.pdf. It has details on few views available in SYS schema that you can make use of, to check  your data volume query. Probably an administrator may be able to help you in carrying out few tasks of cleaning up if that helps.

Regards,

Saritha K

[jtretina]

Hi Saritha,

can you please be more specific which view I can use for checking data utilization? I am not aware of any cleaning tasks which should be in place, are you? But not sure here that it will help since those 30GB of new data are coming up in HANA every day and still dont know why...

Thanks,

Jan

[Saritha_K]

Hi Jan,

You can refer to below views-

M_DATA_VOLUMES - DataVolume statistics

M_DATA_VOLUME_PAGE_STATISTICS - Page usage statistics on data volumes

M_DATA_VOLUME_PAGE_STATISTICS_RESET - Page usage statistics on data

volumes

M_DATA_VOLUME_SUPERBLOCK_STATISTICS - FreeBlockManager Superblock

statistics

One more thing to check  that you can do is as follows-

please do a right click on your system->configuration and monitoring->open administration->system information-

schema size of loaded tables query

size of tables on disk

used memory by tables

size of tables on disk

(to see if any specific table load is causing an issue or not.)

Earlier we had faced an issue of memory and we had carried out few clean up tasks alter system reclaim dataspace/log commands with the help of our administrator.

Regards,

Saritha

[Saritha_K]

One more thing to check--you can also view the memory overview editore of your system to see which is occupying more space in memory allocation.

[jtretina]

Hi,

thanks for those suggestions but unfortunately any of those will help me since I need to know DATA volume utilization or better say what exactly data file in the disk contains (index server data volume .dat file).

In my first post you can see that I checked mem utilization and also table sizes on disk but did not find where or what are those 850GB (current db space 900GB - SUM of table sizes 50GB). Or how to analyze those day increments.

Reclaiming data area is not needed, used / total size ratio is 100%, no free space which can be reclaimed. And log area is not an issue here, I am talking only about data volume growth.

Br, Jan

[lbreddemann]

Hi Jan,

this sounds as if the delta merge operation is not executed in this system. This might be due to some merge decision parameters that are set in a way that basically no table would ever get merged.

Please make sure to check the parameters for the merge decision functions and to set them to DEFAULT.

Concerning your measurements: the size information for the tables are updated only when the tables actually got loaded to memory. So these might be off quite a bit, when you look at the numbers w/o ensuring the tables are loaded at that point in time.

- Lars

[jtretina]

Hi Lars,

I checked delta merge related parameters, also auto merge option for biggest tables and everything is in place.

But you (somehow ) navigated me to check also MVCC statistics and there I found root cause of this issue. By selecting M_MVCC_TABLES I found one external transaction running for almost 30 days blocking garbage collector. Unfortunately the connection id is -1 so I cannot cancel it (AFAIK). Or is there any way how to cancel this zombie session? Probably restart will be needed then.

Br, Jan