how can one monitor daily growth of individual tables

Forum|Forum|7 years ago
June 3, 2019
2 replies
4 views

+2

dgrumann
Participating Frequently

I have seen some queries that use v_monitor.projection_storage and v_catalog.license_audits to come up with compressed and raw sizes for whole schemas, and I know one can use the audit function and the resulting v_catalog.user_audits to see a snapshot of the raw size of individual tables, but I do not know of a way to query the growth of DB tables day by day. Does someone have such a query? Monitoring daily growth of tables could help tuning, sizing and help configure retention settings.

+2

dgrumann
Author
Participating Frequently
Forum|Forum|7 years ago
June 3, 2019

``# example MB growth by day for whole DB, but not by table:
select date_trunc('minute',audit_start_timestamp), cast(database_size_bytes / (1024^3) as decimal(6,0)) as "compressed size GB", cast(database_size_bytes / (1024^2) as decimal(10,0)) as "compressed size MB", cast((database_size_bytes - lead(database_size_bytes,1) over (order by audit_start_timestamp desc)) / (1024^2) as decimal(8,0)) as 'MB growth' from v_catalog.license_audits where database_size_bytes > 0 and audited_data = 'Total' order by audit_start_timestamp desc limit 7;