How to calculate free database disk space

This solution describes how to calculate free database disk space with the use of EXAoperation for EXAStorage based databases. Therefore you need in general: the current disk usage of the database (DB_SIZE) number of active database nodes total disk space of all active nodes number of EXAStorage Volumes (Data, Archive, Temporary) redundancy levels of all EXAStorage Volumes Database disk usage For example, to get the current database disk usage (netto) within the database execute following SQL: select DB_FILE_SIZE* USE /100 from EXA_STATISTICS.EXA_DB_SIZE_LAST_DAY order by 1 desc limit 1; Redundancy To provide a failover mechanism within a EXAStorage Volume, every node stores its own and another nodes data segment. This is the so called “Redundancy”. A Level-2 Redundancy enables the cluster nodes to restore all data segments of the failed node on the standby node. The standby node stores no data until it's pushed in during a fail safety: if a node fails, for whatever reason, the database performs a controlled shutdown and restarts itself with the pushed in standby node. Volume types EXAStorage supports 3 types of Volumes: Data-Volumes (stores persistent data) Archive-Volumes (stores database backups) Temporary-Volumes (if the internal database memory isn't sufficient, data created by queries, e.g. an interim result set, will be swapped to this volume) To enable restoring of persistent and archived data in the case of node failures, the corresponding volumes have typically redundancy level 2. The temporary volume has always redundancy 1, as the data stored in this volume won't be needed after a (fail safety) restart. For each volume you can set a so call quota to limit the size of volume. If 95% of the limit are reached, the database will be automatically shut down to avoid data loss. Calculation without EXAStorage quota If no Maximum Volume Size is set, free database disk space is calculated based on the complete cluster disk space! Open the EXAoperation webinterface and navigate to the EXAStorage overview page. Note down the number of active Database Nodes (count of Master Nodes of the persistent Data-Volume) Master Nodes -> 4 (number_of_nodes) Note down the disk capacity per active Node Space on Disks (e.g. d03_storage) -> 1768GiB per Node (disk_capacity_per_node) Note down the Volume Size of the Archive-Volume Archive-Volume -> 1008 GiB (archive_volume_size) Note down the Volume Redundancy of the Archive- and Data-Volume Data-Volume -> 2 (data_volume_redundancy) Archive-Volume -> 2 (archive_volume_redundancy) Redundancy 2 means, a volume uses twice the disk space displayed in EXAStorage! Fill in collected values and execute the following SQL in EXAplus define number_of_nodes = 4; define disk_capacity_per_node = 1768; define data_volume_redundancy = 2; define temp_volume_reserve = 0.2; define archive_volume_size = 1008; define archive_volume_redundancy = 2; SELECT (( -- overall disk capacity net &number_of_nodes * &disk_capacity_per_node - -- reserved disk space for online backups &archive_volume_size * &archive_volume_redundancy - -- anticipated reserve for temporary volume &temp_volume_reserve * DB_FILE_SIZE - -- size and actual use of the data volume &data_volume_redundancy * DB_FILE_SIZE * USE / 100) -- broken down to the redundancy of the data volume / &data_volume_redundancy) FREE_CAPACITY_FOR_DB_PAYLOAD, -- comparison with the current total payload (&data_volume_redundancy * (MEM_OBJECT_SIZE + AUXILIARY_SIZE + STATISTICS_SIZE)) USED_CAPACITY FROM EXA_STATISTICS.EXA_DB_SIZE_LAST_DAY ORDER BY MEASURE_TIME DESC LIMIT 1; Calculation with EXAStorage quota Within the EXASolution Instance view, look for the field “Maximum Volume Size” (e.g. 8000 GiB). From EXAStorage Overview page note down the temporary Volume Size. Temporary Volume Size -> 1001GiB (temp_volume_size) Fill in collected values and execute the following SQL in EXAplus define maximum_volume_size = 8000; define temp_volume_size= 1001; SELECT (( -- maximum volume size &maximum_volume_size - -- temporary volume size &temp_volume_size - -- size and actual use of the data volume DB_FILE_SIZE * USE / 100) ) FREE_CAPACITY_FOR_DB_PAYLOAD, -- comparison with the current total payload (&data_volume_redundancy * (MEM_OBJECT_SIZE + AUXILIARY_SIZE + STATISTICS_SIZE)) USED_CAPACITY FROM EXA_STATISTICS.EXA_DB_SIZE_LAST_DAY ORDER BY MEASURE_TIME DESC LIMIT 1; Temporary-Volume can heavily vary in size depending on running queries and main memory usage. For this calculation, we reserve 20% of persistent data for the temporary volume.