ASA 17 why database file is so big

[former_SQLA_member1695024]

we have a ASA 17 database which has go live for a year. the database file size keeps growing, like 2-3G a day. I understand that some data are loading to the DB every day. But if I list the tables and sizes, the number doesn't add up. ie, this query give me summary of 25G. ( select sum(table_page_count * DB_PROPERTY('PageSize'))/1024/1024 from SYSTAB WHERE creator = 1; ) and I see the biggest table is just 5G. so why the database file is almost 400G now.

[Breck_Carter]

Chris answered the question "How do I find where all the space is going?"... run dbinfo.

Here's an answer to the question "How do I use the system catalog to find where all the space is going?"

SYSTAB.table_page_count doesn't account for all the used pages in the database file... there's also SYSTAB.ext_page_count and SYSPHYSIDX.leaf_page_count, as well as free pages.

SELECT ( SELECT SUM ( SYSTAB.table_page_count ) FROM SYSTAB ) AS USED_main_table_pages,
       ( SELECT SUM ( SYSTAB.ext_page_count ) FROM SYSTAB ) AS USED_blob_extension_pages,
       ( SELECT SUM ( SYSPHYSIDX.leaf_page_count ) FROM SYSPHYSIDX )  AS USED_index_leaf_pages,
       used_main_table_pages + used_blob_extension_pages + used_index_leaf_pages AS total_USED_pages;

SELECT CAST ( DB_PROPERTY ( 'FreePages' ) AS BIGINT ) AS free_FILE_pages,
       CAST ( DB_PROPERTY ( 'FileSize' ) AS BIGINT ) AS total_FILE_pages,
       CAST ( total_file_pages - free_file_pages AS BIGINT ) AS unfree_FILE_pages;

USED_main_table_pages USED_blob_extension_pages USED_index_leaf_pages      total_USED_pages 
--------------------- ------------------------- --------------------- --------------------- 
               523719                     21264                 40962                585945 

     free_FILE_pages     total_FILE_pages    unfree_FILE_pages 
-------------------- -------------------- -------------------- 
                3471               589911               586440 

I don't know how to explain the difference between total_USED_pages and unfree_FILE_pages ( but I don't care... maybe it's the VAT 🙂

Also, the physical file may actually shrink in size when the database is shut down. When the database is running, the checkpoint log data is appended to the end of the physical file, and that space is freed when a CHECKPOINT is executed on shutdown.

However, that doesn't affect free space.

Sooooo, The "missing space" may be going to extension (blob) pages, index pages, and free pages.