I do no think there is any easy way to do it directly using SQL.

You could do it by using the the output of the commands

        export server filedata=all preview=yes wait=yes
and
        export server filedata=activefiles preview=yes wait=yes

or you could use export node domain=...

The differences between the outputs of the two commands will be the
inactive/deleted file count.

Yes these will take some time to run.



The first one could be optimised to...

  select sum(num_files), sum(logical_mb), sum(physical_mb) -
    from occupancy -
    where -
    stgpool_name in (select stgpool_name from stgpools where pooltype = 
'PRIMARY')

[You need to exclude the copy pools or you will double count.]


There is no way to optimise the second.  A join on the contents and
backups table using ADSM SQL would be a bad thing.  Also, the files
are named different ways in the two tables.  May be you could export
the records to a real database, massage the fields and then do the
matching.


Hope this is of some help

Russell



> Hi all,
>
> My management asked me too find out how much tapepool space is occupied
> by deleted and inactive files. I looked at the SQL Interface and tried
> some select commands, but I can't find how I can get the size of the
> files that are being backed up.
>
> Can someone help me with a SQL query that will display the size of
> deleted and inactive files, or any other solution to get this
> information.
>
> Thanks in advance
>
> Dick