Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    3

    Unanswered: freespace accounting

    I have Sybase 12.5 as well as 11.9.x servers to maintain.

    For proactive space management of the databases, I like to know a simpler way to find out for each database the undelying devices and freespace available in the database as well as all the deives. This helps in adding space to database

    Can anybody help me

  2. #2
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208

    Re: freespace accounting

    For the log segment mgt you can use user defined threshold actions via sp_addthresholdaction.

    For default segment..ummm...you might want to checkout the dynamic space management ability available in ASE 12.5.1

    Cheers,

    Willy

    Originally posted by atmakuri
    I have Sybase 12.5 as well as 11.9.x servers to maintain.

    For proactive space management of the databases, I like to know a simpler way to find out for each database the undelying devices and freespace available in the database as well as all the deives. This helps in adding space to database

    Can anybody help me

  3. #3
    Join Date
    Aug 2003
    Posts
    3

    Re: freespace accounting

    Originally posted by atmakuri
    I have Sybase 12.5 as well as 11.9.x servers to maintain.

    For proactive space management of the databases, I like to know a simpler way to find out for each database the undelying devices and freespace available in the database as well as all the deives. This helps in adding space to database

    Can anybody help me
    Hi Thanks a lot, actually I am looking for sql to find out devices allocated for each database and the freespace available in each device.

    Help will be highly appreciated.
    Venkat

  4. #4
    Join Date
    Jan 2003
    Posts
    26

    Re: freespace accounting

    I think sp_helpsegment will give you the info you're looking for. I have a shell script that calls sp_helpsegment for logsegment and default and dumps the results to files. Then I go through the files to get the info I need. For example, I like to know if the free space is under 1GB so I run something like this:

    isql -U xxx -P xxx -S xxx -i helpseg_default.sql -o results_default
    free_pages=`tail -2 results_default | head -1 | awk '{print $3}'`
    free_mb=$(($free_pages / 512))
    if [ "$free_mb" -lt 1000 ]; then
    msg="Space is low in default segment: $free_mb MB free";
    echo $msg > message;
    cat message | /var/qmail/bin/qmail-inject frank@nospam.com;
    fi

    helpseg_default.sql just calls sp_helpsegment 'default'.

    It ain't the prettiest code but it works well. Does this help at all?

    Frank

  5. #5
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208

    Re: freespace accounting

    G'day.

    The size of a device (in pages) can be found with:

    select (high - low) + 1 from sysdevices where name = <name>

    The space used is

    select sum(size) from sysusages u, sysdevices d where
    u.vstart between d.low and d.high
    and d.name = <name>

    The unused space on the device is the first value - the second value

    Cheers,

    Willy

    Originally posted by atmakuri
    Hi Thanks a lot, actually I am looking for sql to find out devices allocated for each database and the freespace available in each device.

    Help will be highly appreciated.
    Venkat

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •