ASE Database forum - Script/sql to show database usage in MB

Author

Script/sql to show database usage in MB

Tim Ross

2005-10-27, 8:22 am

Does any know where I could find a script or at least the
sql to show me data usages of my databases in MB's. I use
sp_spaceused now but always have to convert the output from
KB to MB. Any insight on this would be appreciated.

2005-10-27, 8:22 am

> Does any know where I could find a script or at least the
> sql to show me data usages of my databases in MB's. I use
> sp_spaceused now but always have to convert the output
> from KB to MB. Any insight on this would be appreciated.

I use the following SQL to capture the space usage within
each database.
select
convert (char(11), getdate(), 111) as 'Date'
, db_name() as 'DBName'
, S.name as 'SegName'
, sum (U.size) /512 as 'TotSpace'
, sum(curunreservedpgs
(db_id(), U.lstart, 0)) /512 as
'FreeSpace'
from syssegments S
, master..sysusages U
where dbid = db_id()
and power (2,S.segment) & U.segmap > 0
group by S.name
order by 1

Carl Kayser

2005-10-27, 8:22 am

I've always disliked the fact that sp_helpdb <db> reports a mix of KB and
MB. I'm used to pages as the unit of size, but the UNIX-oriented admins
prefer MB. So, I allow the default "size" to be configurable as any of
"Pages", "MB", "GB", or "TB" in my procs. Set the default in a table in
sysyemprocs and also allow for additional units per indidual login in the
table as well. Then use a subroutine stored procedure to convert the data
as appropriate. (Read the table and return a division/multiplication factor
depending upon @@maxpagesize and whether your default units are the smallest
(pages) or largest (TB). Then update the values in a temp table.) Note
that sp_configure has allowed one to use P/K/M/G for a while.

So everybody is fairly happy. A headache is that some objects may be more
naturally measured in, say, MB (e.g., tables) versus GB (total database
allocation). So I allow for two levels of granularity in the table. One
could make for a more "expert" system by analyzing the cardinality "on the
fly" within stored procedures and adjusting the units so that the number is
within an M1 to M2 range. But I don't think that the effort is anywhere
near the benefit.

<Tim Ross> wrote in message news:435ce929.2803.1681692777@sybase.com...
> Does any know where I could find a script or at least the
> sql to show me data usages of my databases in MB's. I use
> sp_spaceused now but always have to convert the output from
> KB to MB. Any insight on this would be appreciated.

Michael Peppler

2005-10-27, 8:22 am

On Mon, 24 Oct 2005 07:24:39 -0700, wk wrote:

>
> I use the following SQL to capture the space usage within each database.
> select
> convert (char(11), getdate(), 111) as 'Date'
> , db_name() as 'DBName'
> , S.name as 'SegName'
> , sum (U.size) /512 as 'TotSpace'
> , sum(curunreservedpgs
(db_id(), U.lstart, 0)) /512 as 'FreeSpace'
> from syssegments S
> , master..sysusages U
> where dbid = db_id()
> and power (2,S.segment) & U.segmap > 0 group by S.name
> order by 1

That's fine as long as pages are 2k. It's better to use @@maxpagesize, or
to look up the correct value in spt_values.

Michael
--
Michael Peppler [TeamSybase] mpeppler@peppler.org - http://www.peppler.org/
Sybase DBA/Developer
Sybase on Linux FAQ: http://www.peppler.org/FAQ/linux.html

Carl Kayser

2005-10-27, 8:22 am

<Tim Ross> wrote in message news:435ce929.2803.1681692777@sybase.com...
> Does any know where I could find a script or at least the
> sql to show me data usages of my databases in MB's. I use
> sp_spaceused now but always have to convert the output from
> KB to MB. Any insight on this would be appreciated.

I've always disliked the fact that sp_helpdb <db> reports a mix of KB and
MB. I'm used to pages as the unit of size, but the UNIX-oriented admins
prefer MB. So, I allow the default "size" to be configurable as any of
"Pages", "MB", "GB", or "TB" in my procs. Set the default in a table in
sybsystemprocs and also allow for additional units per individual login in
the
table as well. Then use a subroutine stored procedure to convert the data
as appropriate. (Read the table and return a division/multiplication factor
depending upon @@maxpagesize and whether your default units are the smallest
(depends upon @@maxpagesize) or largest (TB). Then update the values in a
temp table.) Note that sp_configure has allowed one to use P/K/M/G for a
while.

So everybody is fairly happy. A headache is that some objects may be more
naturally measured in, say, MB (e.g., tables) versus GB (e.g., total
database
allocation). So I allow for two levels of granularity in the table. One
could make for a more "expert" system by analyzing the cardinality "on the
fly" within stored procedures and adjusting the units so that the number is
within an low to high range. But I don't think that the effort is anywhere
near the benefit.

Sponsored Links