Skip to Content
0

Can ASE MDA tables be used to monitor database growth?

Apr 11 at 08:18 PM

62

avatar image
Former Member

Hello

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Mark A Parsons Apr 11 at 10:01 PM
1

You'll need to consider at what level you're looking to monitor database space usage.

If you're simply looking for space used per segment/device, you may be able to get by with periodically sampling monDeviceSegmentUsage.

If you're looking for object level metrics (eg, you want to drill down on the segment/device usage to see what table, index, blob chain is using up the most space), then you may be able to get by with sampling monCachedObject though this is likely to be incomplete as it won't report on objects not in cache at the time you take your sampling.

Regardless of the granularity of the desired stats, you'll likely have better luck rolling your own code. If you're unfamiliar with the system tables and/or the builtin functions related to space management, you should consider looking at the source code for space-related system procs ... sp_spaceused, sp_helpsegment, sp_helpdb ... what you're looking for are space-related function calls as well as space-related queries against system tables.

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Apr 12 at 02:36 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Apr 12 at 08:07 PM
0

thank you all !

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Apr 12 at 02:30 PM
0

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.

Share
10 |10000 characters needed characters left characters exceeded