SAP Sybase ASE 15.7 Query Database Free Space and Percent Used




Example Output showing Total Data Space, Data Space Free, Percent Data Space Used


 AL2016          |Data_Total_MB=100      |Free_MB=96.3          |Used=3.68%
 MOVE_TEST       |Data_Total_MB=5        |Free_MB=1.6           |Used=66.32%
 TEMPDB_2        |Data_Total_MB=50       |Free_MB=46.4          |Used=7.04%
 altest_new      |Data_Total_MB=4        |Free_MB=0.6           |Used=83.59%
 dbccdb          |Data_Total_MB=100      |Free_MB=69.5          |Used=30.40%
 master          |Data_Total_MB=26       |Free_MB=13.4          |Used=48.39%
 model           |Data_Total_MB=6        |Free_MB=2.6           |Used=55.85%
 step_test       |Data_Total_MB=20       |Free_MB=16.6          |Used=16.87%
 primary_test    |Data_Total_MB=500      |Free_MB=433.7         |Used=13.25%
 pushpullsigns   |Data_Total_MB=545      |Free_MB=310.3         |Used=43.05%
 raw_test        |Data_Total_MB=500      |Free_MB=353.7         |Used=29.24%
 second_disk_fs  |Data_Total_MB=500      |Free_MB=364.8         |Used=27.03%
 segtest         |Data_Total_MB=60       |Free_MB=53.9          |Used=10.13%
 sybmgmtdb       |Data_Total_MB=75       |Free_MB=49.7          |Used=33.70%
 sybsecurity     |Data_Total_MB=300      |Free_MB=294.4         |Used=1.85%
 sybsystemdb     |Data_Total_MB=12       |Free_MB=8.4           |Used=29.94%
 sybsystemprocs  |Data_Total_MB=172      |Free_MB=9.8           |Used=94.25%
 tempdb          |Data_Total_MB=106      |Free_MB=102.2         |Used=3.55%
 test_tempdb     |Data_Total_MB=110      |Free_MB=106.2         |Used=3.41%

(19 rows affected)

SQL Query for Output Above

select convert(char(16), db_name(d.dbid))
+ "|Data_Total_MB=" + convert(char(9), ceiling (sum(case when u.segmap != 4 then u.size/1048576.*@@maxpagesize end )))
+ "|Free_MB=" + convert(char(14), (convert(numeric(8,1),
           ((sum(case when u.segmap != 4 then u.size/1048576.*@@maxpagesize end ))) - (sum(case when u.segmap != 4 then size 
           - curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs) end)/1048576.*@@maxpagesize))))
+ "|Used=" + rtrim(convert(char(9), (convert(numeric(12,2),
           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)))))) 
+ "%"
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)

Original Query

The original query was taken from stackexchange / benohead.com:

http://dba.stackexchange.com/questions/71954/getting-database-usage-of-all-the-database-at-once-in-sybase

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

The output for the query above has been modified to show only data segments. Various changes have been made to the formatting.

Log Free and Used Query

The corresponding separate log query shows just the databases with logsegments.

lct_admin("reserved_for_rollbacks",d.dbid) has been added into the query to take account of log space reserved for rollback during an active transaction.

 AL2016          |Log_Total_MB=50       |Free_MB=49.7        |Used=0.45%
 MOVE_TEST       |Log_Total_MB=5        |Free_MB=4.9         |Used=1.01%
 altest_new      |Log_Total_MB=2        |Free_MB=1.9         |Used=2.34%
 dbccdb          |Log_Total_MB=20       |Free_MB=19.8        |Used=0.54%
 step_test       |Log_Total_MB=10       |Free_MB=9.9         |Used=0.82%
 primary_test    |Log_Total_MB=500      |Free_MB=379.6       |Used=24.06%
 pushpullsigns   |Log_Total_MB=736      |Free_MB=733.0       |Used=0.39%
 raw_test        |Log_Total_MB=500      |Free_MB=498.0       |Used=0.39%
 second_disk_fs  |Log_Total_MB=500      |Free_MB=77.8        |Used=84.43%
 segtest         |Log_Total_MB=20       |Free_MB=16.1        |Used=19.35%
 sybsecurity     |Log_Total_MB=100      |Free_MB=98.9        |Used=1.03%

(11 rows affected)



select convert(char(16), db_name(d.dbid))
+ "|Log_Total_MB=" + convert(char(9), ceiling(sum(case when u.segmap = 4 then u.size/1048576.*@@maxpagesize end)))
+ "|Free_MB=" +
convert(char(12),
(convert(numeric(8,1),
(lct_admin("logsegment_freepages",d.dbid) - 1.0 * lct_admin("reserved_for_rollbacks",d.dbid)) /1048576.*@@maxpagesize
)))
+ "|Used=" +
rtrim(convert(char(7),
(convert(numeric(12,2),
(100 * (1 - 1.0 *
(lct_admin("logsegment_freepages",d.dbid) - 1.0 * lct_admin("reserved_for_rollbacks",d.dbid))
/ sum(case when u.segmap in (4, 7) then u.size end)))
)))) + "%"
from master..sysdatabases d, master..sysusages u
where u.dbid = d.dbid  and d.status not in (256,4096)
group by d.dbid
having (sum(case when u.segmap = 4 then u.size/1048576.*@@maxpagesize end)) != NULL
order by db_name(d.dbid)



Push Symbol. Push Symbol. Door logos



Alastair Cook email:
cookalastair @ btinternet.com

Page created - 12th Nov 2016


Push Pull Door