Skip to Content

Can ASE MDA tables be used to monitor database growth?

Hello

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Apr 11 at 10:01 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 12 at 02:36 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 12 at 08:07 PM

    thank you all !

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 12 at 02:30 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded