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).
No comments:
Post a Comment