Can ASE MDA tables be used to monitor database growth?

[former_member300534]

Hello

can MDA in Sybase / SAP be used for database space growth?

[former_member300534]

thank you all !

[former_member300534]

ok thank you ... what I'm looking for is for a tool or something that will gather database space growth overtime , i do have a "own code" but requires more manual work

isql -Usa -Ppwd -w 300 -o ${LOG} << EOF

use master

go

select * from sysservers

go

select "Database Name"=substring(d.name,1,20), "Owner"=substring(l.name,1,20), "Status"=d.status, "Creation Date"=substring(d.crdate,1,20), "Last dump Tran"=substring(d.dumptrdate,1,20), "Size (MB)"= isnull(sum(u.size) *2 /1024, 0), "Unallocated (MB)" = isnull(sum(u.unreservedpgs) *2 / 1024, 0) from sysdatabases d, syslogins l, sysusages u where d.suid = l.suid and d.dbid = u.dbid group by d.dbid,l.suid order by 1,2

go

select "Database Name"=substring(d.name,1,20), "Dbid"=d.dbid, "Segmap"=u.segmap, "LStart"=u.lstart, "Seg. Size (MB)"=isnull(u.size / 512, 0), "vdevno" = convert(numeric(2,0), u.vstart / power(2, 24)), "Free (MB)"=isnull(u.unreservedpgs / 512, 0) from sysdatabases d, sysusages u where d.dbid = u.dbid order by 2,4

go

select * from sysusages

go

select "Device Name" = substring(d.name, 1, 15), "Physical Name" = substring(d.phyname, 1, 64), vdevno = convert(numeric(2,0), d.low / power(2, 24)), "Size (MB)" = (d.high - d.low +1 ) * 2 / 1024, "Reserved (MB)" = isnull(sum(u.size) * 2 / 1024, 0), "Left (MB)" = (d.high - d.low +1) * 2 / 1024 - isnull(sum(u.size) * 2 / 1024, 0) from sysdevices d, sysusages u where d.status & 2 = 2 and u.vstart / power(2, 24) =*d.low / power(2, 24) group by substring(d.name, 1, 15), substring(d.phyname, 1, 64), d.low / power(2, 24), (d.high - d.low +1) * 2 / 1024 order by vdevno compute sum((d.high - d.low +1) *2 /1024), sum(isnull(sum(u.size) * 2 / 1024, 0)), sum((d.high - d.low + 1) *2 / 1024 - isnull(sum(u.size) * 2 /1024, 0))

go

select "Sybase version"=substring(@@version,1,120)

go

[bonusbrevis]

Take a look at sp_spaceusage - it has the capacity to log, collect, and report on summaries and details at the object level, it's what we have used for years to track table growth.

For just database data and log space growth, Mark is correct, you are better off writing your own. We read off sysdatabases, sysdevices, sysusages and some spt_values to get a daily snapshot, which is then collected, aggregated and fed to our analytics.