Below is the SQL statement that calculates how much space is allocated and used by data and log segments in Sybase.
SELECT "Database Name" = CONVERT(char(20), db_name(D.dbid)),
"Data Size" =
STR(SUM(CASE
WHEN U.segmap != 4 THEN U.size*@@maxpagesize/1048576
END
),10,1),
"Used Data" =
STR(SUM(CASE
WHEN U.segmap != 4
THEN size -
curunreservedpgs(U.dbid, U.lstart, U.unreservedpgs)
END
)
*@@maxpagesize/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 in (4, 7) THEN U.size*@@maxpagesize/1048576
END),10,1),
"Free Log" =
STR(lct_admin("logsegment_freepages",D.dbid)*@@maxpagesize/1048576,10,1),
"Log Full%" =
STR(100 * (1 - 1.0 * lct_admin("logsegment_freepages",D.dbid)
/ SUM(CASE WHEN U.segmap in (4, 7) 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) OR D.dbid = 2) AND (D.status != 256))
GROUP BY D.dbid
ORDER BY db_name(D.dbid)
|
Some Notes
1.- This part of WHERE clause:
(((D.dbid > 3) AND (D.dbid < 31513) OR D.dbid = 2)
means "all user databases ("dbid from 4 to 31512") and tempdb ("dbid=2").
2.- @@maxpagesize - pagesize, the server level parameter - can be 4K (4096), standard in Sybase 15.5, but can be changed when the server is being configured, or 2K (2048) standard for older versions.
3.- U.segmap in (4, 7) - covers both cases of log pages location: 4 - separate log segment, 7 - shared by log and datapages.
Allocation and usage numbers reported in the latter case will be the same for data and log (as the same space is shared by both).
