1

How can I get the database usage of all the databases at once?

sp_spaceused and sp_helpdb <dbname> only gives the usage one at a time.

I use the Sybase Central client.

1
  • logsegment_freepages is invalid column in ASE16
    – Michael
    Commented Mar 9, 2018 at 23:16

3 Answers 3

3

Below is the script that I use --- master..sysdatabases& master..sysusages

--- Source : http://benohead.com/sybase-size-of-data-and-log-segments-for-all-databases/

select db_name(d.dbid) as db_name,
ceiling(sum(case when u.segmap != 4 then u.size/1048576.*@@maxpagesize end )) as data_size,
ceiling(sum(case when u.segmap != 4 then size - curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs) end)/1048576.*@@maxpagesize) as data_used,
ceiling(100 * (1 - 1.0 * sum(case when u.segmap != 4 then curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs) end) / sum(case when u.segmap != 4 then u.size end))) as data_used_pct,
ceiling(sum(case when u.segmap = 4 then u.size/1048576.*@@maxpagesize end)) as log_size,
ceiling(sum(case when u.segmap = 4 then u.size/1048576.*@@maxpagesize end) - lct_admin("logsegment_freepages",d.dbid)/1048576.*@@maxpagesize) as log_used,
ceiling(100 * (1 - 1.0 * lct_admin("logsegment_freepages",d.dbid) / sum(case when u.segmap in (4, 7) then u.size end))) as log_used_pct 
from master..sysdatabases d, master..sysusages u
where u.dbid = d.dbid  and d.status not in (256,4096)
group by d.dbid
order by db_name(d.dbid)
2

You should probably create a .sql script that can run and return the value for multiple commands.

A script can be easily created using the following format:

set nocount on
select "sp_helpdb " + name + char(10) + "exec " + name + "..sp_spaceused" + char(10) + "go"
go

This will print out a list of commands that looks like this:

sp_helpdb DATABASE_01
exec DATABASE..sp_spaceused
go
sp_helpdb DATABASE_02
exec DATABASE..sp_spaceused
go
...

This output can be captured into a script file, and then executed all at once.

2

Kin's query works well, unless there are incorrect entries in sysusages. I have run into that error. Sybase automatically ignores the bad entries, but this script could be thrown off. By checking if the vdevno is >= 0 will make sure all counted devices are real / usable devices.

I changed the script like so:

select db_name(d.dbid) as db_name,
ceiling(sum(case when u.segmap != 4 and vdevno >= 0 then (u.size/1048576.)*@@maxpagesize end )) as data_size_MB,
ceiling(sum(case when u.segmap != 4 and vdevno >= 0 then size - curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs) end)/1048576.*@@maxpagesize) as data_used_MB,
ceiling(100 * (1 - 1.0 * sum(case when u.segmap != 4 and vdevno >= 0 then curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs) end) / sum(case when u.segmap != 4 then u.size end))) as data_used_pct,
ceiling(sum(case when u.segmap = 4 and vdevno >= 0 then u.size/1048576.*@@maxpagesize end)) as log_size_MB,
ceiling(sum(case when u.segmap = 4 and vdevno >= 0 then u.size/1048576.*@@maxpagesize end) - lct_admin("logsegment_freepages",d.dbid)/1048576.*@@maxpagesize) as log_used_MB,
ceiling(100 * (1 - 1.0 * lct_admin("logsegment_freepages",d.dbid) / sum(case when u.segmap in (4, 7) and vdevno >= 0 then u.size end))) as log_used_pct 
from master..sysdatabases d, master..sysusages u
where u.dbid = d.dbid  and d.status != 256
group by d.dbid
order by db_name(d.dbid)
1
  • segmap is a bitmap and therefore should be tested with bitwise operations, eg, your query could mishandle entries where segmap = 5/6/12 (log segment combined with system/default/user-defined segments) [and, yes, Kin's answer has the same issue]; also keep in mind that vdevno was introduced with ASE 15.0, so the query will generate a syntax error if used in pre-ASE 15.0 (eg, ASE 11.x/12.x)
    – markp-fuso
    Commented Dec 20, 2017 at 21:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.