I've just added a new 256mb chunk to a 10GB database and it has already become full.

We have also deleted 3 years worth of data (from 6 years in the database) a little while ago. This was followed by a CLUSTER INDEX on the larger tables to free up some space (forcing a table rebuild).

Is there anyway of finding out what has gone into the new chunk?

Is thee any reason the empty slots in the database are not appearing to be used.


Thanks for yuor help.

Looking into this further, I think the reason the new chunk filled up so quickly was...

1) A backlog in flushing the logical logs could be carried out therefore writing to the new chunk so quickly.

2) Extents on the tables caused more disk usage than purely data/index records to be used up.

This just leaves me with wondering why the deleted data doesn't appear to have freed up approximately half the space.

Is there any way of checking how much space is used by the data and how much is used by the index in a table?

Cheers,
Roy.

database sysmaster;
select b.dbsname, b.tabname, hex(a.partnum), a.nextns, a.fextsiz FIRST, a.nextsiz NEXT, a.nptotal, a.npused, a.npdata
from sysptnhdr a, sysptprof b
where a.partnum = b.partnum

will give you a fine overview of number of extents per table together with their first extent size, next extent size, number of pages total(total number of pages allocated in extents), used (total number of pages used - ie one record in one page is 1 page used), data (total number of pages in data.

select * from sysextents will give you an extent overview per extent.

here you can find the chunk id (version 9.4)
prior to version 9.4, use hex(start). the first three positions are the number of the cunk. the rest is the offset in that chunk.