SQL Statement to get all tenant and system db size in GB

[Former Member]

Hi Experts,

Could you help me with the sql statement to get DB size of system and its tenant in one go . We have a multi tenant Hana database with many tenants . We require this for monitoring and some cleaning purpose . Could you pls help me with the statement as am new to the HANA platform

Thanks !!

[former_member205280]

Hi,

If I understand your question correctly, you're talking about the HANA database size (not the memory load size) right?

Here's some of the SQL query you may want to try:

--Total size of both data and log volume with database name, host name, server name

SELECT A.DATABASE_NAME, A.HOST, A.SERVICE_NAME, B.DATA AS DATA_GB, C.LOG AS LOG_GB  FROM "SYS"."M_VOLUMES_" AS A INNER JOIN (SELECT VOLUME_ID, DATABASE_NAME, ROUND(DATA_SIZE/1024/1024/1024, 3) AS DATA FROM "SYS"."M_VOLUME_SIZES_" WHERE LOG_SIZE =-1) AS B ON A.VOLUME_ID = B.VOLUME_ID AND A.DATABASE_NAME = B.DATABASE_NAME INNER JOIN

(SELECT VOLUME_ID, DATABASE_NAME, ROUND(LOG_SIZE/1024/1024/1024, 3) AS LOG FROM "SYS"."M_VOLUME_SIZES_" WHERE DATA_SIZE =-1) AS C ON B.VOLUME_ID = C.VOLUME_ID AND B.DATABASE_NAME = C.DATABASE_NAME;

--Total size of data volume by database name for each hosts

SELECT A.DATABASE_NAME, A.HOST, SUM(B.DATA) AS DATA_GB FROM "SYS"."M_VOLUMES_" AS A INNER JOIN (SELECT VOLUME_ID, DATABASE_NAME, ROUND(DATA_SIZE/1024/1024/1024, 3) AS DATA FROM "SYS"."M_VOLUME_SIZES_" WHERE LOG_SIZE =-1) AS B ON A.VOLUME_ID = B.VOLUME_ID AND A.DATABASE_NAME = B.DATABASE_NAME GROUP BY A.DATABASE_NAME, A.HOST;

--Total size of data volume by database name

SELECT A.DATABASE_NAME, SUM(B.DATA) AS DATA_GB FROM "SYS"."M_VOLUMES_" AS A INNER JOIN (SELECT VOLUME_ID, DATABASE_NAME, ROUND(DATA_SIZE/1024/1024/1024, 3) AS DATA FROM "SYS"."M_VOLUME_SIZES_" WHERE LOG_SIZE =-1) AS B ON A.VOLUME_ID = B.VOLUME_ID AND A.DATABASE_NAME = B.DATABASE_NAME GROUP BY A.DATABASE_NAME;

Regards,

Michael

[nikunjmehta2290]

Hello @ Michael Hu,

Thanks for the sharing information. But , How to see particular database size in HANA?

SAP Business One HANA

Kind Regards,

Nikunj