Skip to Content
avatar image
Former Member

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

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 !!

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    Oct 05, 2016 at 01:26 PM

    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

    Add comment
    10|10000 characters needed characters exceeded