How to check free space in Sybase(ASE) database

If we would like to check free and occupied space in devices with data and logs in our Sybase(ASE) databases we can use script as below:

declare @pagesize int
select @pagesize=(select @@maxpagesize)
SELECT “Database Name” = CONVERT(char(20), db_name(D.dbid)),
“Data Size” = STR(SUM(CASE WHEN U.segmap != 4 THEN U.size*@pagesize/1048576 END),10,1),
“Used Data” = STR(SUM(CASE WHEN U.segmap != 4 THEN size – curunreservedpgs(U.dbid, U.lstart, U.unreservedpgs)END)*@pagesize/1048576,10,1),
“Data Full%” = STR(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)),9,1) + “%”,
“Log Size” = STR(SUM(CASE WHEN U.segmap = 4 THEN U.size*@pagesize/1048576 END),10,1),
“Free Log” = STR(lct_admin(“logsegment_freepages”,D.dbid)*@pagesize/1048576,10,1),
“Log Full%” = STR(100 * (1 – 1.0 * lct_admin(“logsegment_freepages”,D.dbid) /
SUM(CASE WHEN U.segmap = 4 THEN U.size END)),8,1) + “%”
FROM master..sysdatabases D,
master..sysusages U
WHERE U.dbid = D.dbid
AND ((D.dbid > 3) AND (D.dbid < 31513) AND (D.status != 256))
GROUP BY D.dbid
ORDER BY db_name(D.dbid)
go

We are saving it to file, eg: check.sql and execute. Example:

isql -Usa -P -i check.sql

Jeśli chemy wyświetlić dane dla wszystkich baz ( łącznie z systemowymi) kasujemy ze skryptu linię :

If ew want to get information for all databases (including system databases) we need to delete from script one line:

AND ((D.dbid > 3) AND (D.dbid < 31513) AND (D.status != 256))

One Response to “How to check free space in Sybase(ASE) database”

  1. DodgyBugga says:

    In case others have seen the same issue using this script, because we have some larger databases and (unfortunately) some still on Sybase ASE 12.5.4, I had an arithmetic overflow error when running this script.
    I had to change the first line to declare @pagesize numeric(19,0). If you are using only 15.0+ then change it to declare @pagesize bigint.

    Thanks for the script, it has come in very handy.

Leave a Response