Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003
    Posts
    12

    Question Unanswered: Not recovering disk space after delete/drop

    How do I recover the disk space when I have deleted the data in a table?
    I have too many tables to drop individually - so I tried to drop the chunks. I couldn't drop the chunk because it was the first chunk in the dbspace, so I dropped the dbspace. This did not work, so I dropped the database. This also did not work because when I recreated the database, a GB of space was taken up with just the table definitions.

    What am I missing??

    Thanks...

  2. #2
    Join Date
    Oct 2003
    Posts
    706

    Wink

    I suggest, "don't worry about it." A database will recycle the disk-space and that's what you want it to do.

    In many operating systems, shrinking and extending the physical size of a file is an expensive operation. It is much less expensive to recycle it. If disk-space is at a premium, a far more satisfactory solution is to .. buy more disks.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  3. #3
    Join Date
    Jun 2003
    Posts
    12

    Unhappy

    It doesn't appear to be 'recycling' the space.

    I reloaded the table, and I have 2x more disk space allocated (without the indexes) then I should because it did not release it or recycle it!

    I am on Linux RH 2.4 kernel, IDS 9.40.UC2 engine with raw disk for storage. Isn't there anyway to release it?

  4. #4
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780
    Originally posted by jenifer
    It doesn't appear to be 'recycling' the space.

    I reloaded the table, and I have 2x more disk space allocated (without the indexes) then I should because it did not release it or recycle it!

    I am on Linux RH 2.4 kernel, IDS 9.40.UC2 engine with raw disk for storage. Isn't there anyway to release it?
    Hi Jenifer,

    Please run this sripts:
    -----------------------------------------------------------------------------------
    This look for spaces:

    [ `uname -a|grep -c 'AIX'` -eq 1 ] && bk=256 || bk=512
    raya="--------------------------------------------------------------------------
    --"
    echo "Space used in On-Line `echo $INFORMIXSERVER` al `date +%d/%m/%Y-%H:
    %M`"
    echo $raya
    {
    dbaccess sysmaster <<!
    select dbsnum,
    (
    select name[1,18]
    from sysdbspaces
    where syschunks.dbsnum=sysdbspaces.dbsnum
    ) dbspace,
    trunc((sum(chksize))/$bk,0) total,
    trunc(((sum(chksize)-sum(nfree)))/$bk,0) ocupado,
    trunc(sum(nfree)/$bk,0) libre
    from syschunks
    group by 1,2
    order by 1
    !
    } 2>/dev/null | sed '/^$/d'|pg -p "(%d) <Enter>=Continue - <q>uit:"
    echo $raya
    -----------------------------------------------------------------------------------

    And this for look amount extents for table:

    usage()
    {
    echo "Modo de uso: $0 <base> [<tabla>]" ; exit
    }

    info()
    {
    dbaccess $base 2>/dev/null << EOF
    set isolation to dirty read;
    select partnum,tabname,fextsize
    from systables
    -- where tabid > 99
    where tabtype = 'T'
    into temp tmpbase with no log;
    select t.tabname Tablas, s.rowsize rowsize, s.nrows filas,
    s.nextns Extents, t.fextsize First_Ext, (s.nptotal*2) Extent_Ini
    from sysmaster:sysptnhdr s, tmpbase t
    where s.partnum = t.partnum
    order by 4 desc
    EOF
    }

    traeinfo2()
    {
    nn=`{ echo "select count(*) xx from systables \
    where tabname = '$tab';"|dbaccess $base 2>/dev/null; }|sed 's/xx//g'`
    [ $nn -eq 0 ] && echo "La tabla '$tab' en base '$base' no existe" && exit
    zz=`{ echo "select tabid,flags xx from systables \
    where tabname = '$tab';"|dbaccess $base 2>/dev/null; }|sed -e 's/xx//g' -e 's/ta
    bid//'`
    nn=`echo $zz|awk '{print $1}'`
    tip=`echo $zz|awk '{print $2}'`
    [ "$tip" = "8" ] && tipo="RAW" || tipo="STD"
    frg=`{ echo "select count(*) xx from sysfragments \
    where tabid = $nn;"|dbaccess $base 2>/dev/null; }|sed '/xx/d'`
    [ $frg -ne 0 ] && frag="Fragmentada" && tabid=$nn || frag="" || tabid=0
    if [ $frg -ne 0 ] ; then
    echo "\nTabla: $tab - $frag - Tipo: $tipo -"
    sql="select sum(nextns) nextns \
    from sysmaster:sysptnhdr \
    where partnum in (select partn from $base:sysfragments where tabid=$nn \
    and fragtype = 'T')"
    ne=`echo $sql | dbaccess sysmaster 2>/dev/null| sed -e '/^$/d' -e '/nextns/d'`
    [ `echo $ne|grep -c "Error"` -gt 0 ] && {
    echo "Hubo errores al acceder a la base."
    exit
    }
    else
    echo "\nTabla: $tab - No Fragmentada - Tipo: $tipo -"
    sql="select sum(nextns) nextns \
    from sysmaster:sysptnhdr \
    where partnum in (select partnum from $base:systables where tabid=$nn )"
    ne=`echo $sql | dbaccess sysmaster 2>/dev/null| sed -e '/^$/d' -e '/nextns/d'`
    [ `echo $ne|grep -c "Error"` -gt 0 ] && {
    echo "Hubo errores al acceder a la base."
    exit
    }
    fi
    nn=`echo $ne|sed 's/ //g'`
    echo "La tabla $tab de la base $base posee: $nn extent(s)"
    exit
    }


    ## MAIN ##
    case $# in
    1) base=$1; info $base |more ;;
    2) base=$1; tab=$2; traeinfo2 $base $tab ;;
    *) usage ;;
    esac
    ## END MAIN ##
    Gustavo.

  5. #5
    Join Date
    Oct 2003
    Posts
    706
    Originally posted by jenifer
    It doesn't appear to be 'recycling' the space.

    I reloaded the table, and I have 2x more disk space allocated (without the indexes) then I should because it did not release it or recycle it!

    I am on Linux RH 2.4 kernel, IDS 9.40.UC2 engine with raw disk for storage. Isn't there anyway to release it?

    I don't know how you went about "reloading the table" but all sorts of things -- like transactions -- can figure into your results in unexpected ways. Try reloading a couple of more times. If it still isn't going down then you must have a problem indeed.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

Posting Permissions

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