Finding Size of Instance Database

[Mike McCall]

Is there a way to see the size of the database being used for your instance? Before I open a support ticket with this question, I thought I'd check if anyone knew an on-demand method available to administrators.

Obviously, I would appreciate granularity (size per table, etc.), but even just a single number would be great (e.g., "your instance's database is currently 1.2 GB").

[Jeff Boltz1]

DK,

Thank you for the suggestion.

Could you provide further details on how you were able to ascertain the size of the database using instance cloning?   I think that could be useful since cloning happens frequently enough to provide on-going metrics.

Best,
Jeff

[Deepak Kumar5]

Add Related list Database Table clone. It will show the data showing below.

[kjcv912]

Hi,

believe we are a relative big user of Performance Analytics, pa_scores table is over 616 000 000 rows.

I have scheduled a daily job counting some of the PA tables so we understand the growth.

This weekend are we applying Helsinki Patch 5 so I have reviewed some sysout from different platform jobs,

come across one table that might address the topic discussed here.
Check out table "UsageAnalytics Counts", at least we have statistics for 117 tables including pa_scores that is my favourite table:-)

List table by typing this command in the navigation filter field "UsageAnalytics Counts.list"

Here is the function I use when counting limited number of PA tables

function doit(table) {

  var count = new GlideAggregate(table);

  count.addAggregate('COUNT');

  count.query();

  gs.sleep(1000);// Wait for 1 second...

  if(count.next())

  gs.log("PA: Total number of records in '" + table + "': " + count.getAggregate('COUNT') , logSource);

  gs.sleep(1000);// Wait for 1 second...

  count.close();

}

[drjohnchun]

Yet another way to get exactly what was originally asked for is to use gs.sql(), if you're on Fuji or older release (it no longer works from Geneva):

gs.sql("SELECT CONCAT('YOUR INSTANCE DATABASE IS CURRENTLY ', ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 1), ' GB') DB_SIZE_GB FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'");

gives (based on table sizes; took 180 ms)

DB_SIZE_GB

YOUR INSTANCE DATABASE IS CURRENTLY 2.8 GB

To get the size per table for all tables, with the largest first, use

gs.sql("SELECT TABLE_NAME, TABLE_ROWS, ROUND(((data_length + index_length) / 1024 / 1024), 2) TABLE_SIZE_MB FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' ORDER BY 3 DESC");

gives (took 308 ms)

TABLE_NAME, TABLE_ROWS, TABLE_SIZE_MB

sys_upgrade_history_log, 340131, 1458.94

sys_attachment_doc, 20109, 220.16

sys_user_token, 149572, 82.78

sys_audit, 55394, 61.13

syslog_transaction0006, 72771, 59.75

syslog_transaction0002, 65791, 56.73

syslog_transaction0003, 68679, 56.73

syslog_transaction0004, 57225, 55.67

sys_metadata, 72777, 53.97

syslog_transaction0005, 58621, 51.81

usageanalytics_count, 35119, 40.75

syslog_transaction0001, 42843, 38.75

syslog_transaction0007, 34261, 31.80

...

To use gs.sql(), you have to elevate privilege to security_admin and use "Scripts - Background" from the Navigator.

WARNING: misuse of gs.sql() may cause unrecoverable damage to your instance.

Please feel free to connect, follow, mark helpful / answer, like, endorse.

John Chun, PhD PMP

[Mike McCall]

I'd say it's a shame that the direct SQL access was removed, but I assume it's because of that "unrecoverable damage" you pointed out.

I'm also assuming that the ODBC driver is the only officially supported/recommended way to run SQL queries now.