Friday 27 January 2012

How to calculate current space and maximum perm space of a user or database?

We can use the view DBC.ALLSPACE to calculate current perm space and maximum perm space.
However the catch here is that space in DBC.ALLSPACE is divided in to AMPs(.ie VPROCs).
Hence we need to do a SUM function on the column CURRENTPERM and MAXPERM.

If we need to calculate current and max perm space for database 'DATABASE1' then following would be the query:

Select SUM(CURRENTPERM),SUM(MAXPERM) from DBC.ALLSPACE where Databasename='DATABASE1';

We may also need to calculate space taken by a table.
For that we can modify the above query as below:

Select SUM(CURRENTPERM),SUM(MAXPERM) from DBC.ALLSPACE where Databasename='DATABASE1' and tablename='TESTTABLE';

This can also be achieved using the view DBC.TableSize view. 

No comments:

Post a Comment