Check Database Space Being Used

Scenario

Capturing and manipulating data does itself require disk storage. Sometimes it is useful to know how much space the system is using and how fast that usage is growing.

SQL Server

Where PhixFlow is running on  a SQL Server installation, set up a PhixFlow Model to analyse the current PhixFlow instance, to determine and detail how much disk space is being used.

Solution:

• Create tables to extract data from the internal PhixFlow tables.
• For each table that is part of a PhixFlow model, query SQL Server for its data size / disk requirements. Use the built in SQL Server stored procedure : sp_spaceused.
• As an additional exercise, determine the number of records, the number of tables and the archive details for each table.

Oracle

Where PhixFlow is running on an Oracle installation, there is no simple way to determine the amount of space used. The PhixFlow Model to analyse the current PhixFlow instance, must count or estimate field size and table size, table by table, field by field.

Solution:

• Create tables to extract data from the internal PhixFlow tables.
• For each table that is part of a PhixFlow model, query the metadata table for its field types, and data size / disk requirements.
• In the case of varchar fields, take a statistically significant random sample of actual data record fields to determine the average length of varchars stored.
• As an additional exercise, determine the number of records, the number of tables and the archive details for each table.