Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    7

    Unanswered: Sizing a database

    Quick easy question ... I presume....

    We are looking to migrate a database from one server to another - what is the easiest way to determine the size of the current database? Is it as simple as 'onstat -d', adding up the number of pages for each chunk & multiply by the page size?

    Thanks in advance

  2. #2
    Join Date
    Feb 2004
    Location
    Tunisia
    Posts
    17
    Hi,

    u can run the SQL script :
    select

    sum(chksize) chsize

    from syschunks,

    sysdbspaces

    where syschunks.dbsnum = sysdbspaces.dbsnum

    and name ="<dbs_name>"

    where <dbs_name> is the name of dbspace
    where is stored your database

    * chsize in pages

  3. #3
    Join Date
    Feb 2004
    Location
    Tunisia
    Posts
    17
    of course u must query the sysmaster database

  4. #4
    Join Date
    May 2004
    Posts
    1

    Measure the size of a database

    Keep in mind that, if you use blobspaces, their page sizes are probably different from your normal page size.
    Here's a query to report the actual size (in GB) of each dbspace on your server, including blobspaces. The script assumes your page size is 2K. If not, just change the 2048's to the correct size for your platform.

    select a.name as dbspace,
    round(sum(b.chksize)/512000,2) as total_gb,
    decode(b.is_blobchunk, 1,
    round(sum(b.nfree)/(512000 / (a.bpagesize / 2048)), 2),
    round(sum(b.nfree)/512000,2)
    ) as free_gb,
    decode(b.is_blobchunk, 1,
    round((sum(b.nfree) * (a.bpagesize / 2048))/sum(b.chksize) * 100, 2),
    round(sum(b.nfree)/sum(b.chksize) *100,2)
    ) as percent_free
    from sysdbstab a,
    syschunks b
    where a.dbsnum = b.dbsnum
    group by a.name, a.bpagesize, b.is_blobchunk
    order by 1,4

  5. #5
    Join Date
    Jul 2003
    Location
    Beijing China
    Posts
    36
    DATABASE sysmaster;
    SELECT stn.dbsname db_name,
    SUM
    (
    sti.ti_npused *
    (
    select sh_pagesize from sysshmvals
    )/1024/1024
    ) mb_used,

    SUM
    (
    sti.ti_nptotal *
    (
    select sh_pagesize from sysshmvals
    )/1024/1024
    ) mb_total
    FROM systabnames stn, systabinfo sti, sysdatabases sdb
    WHERE stn.partnum = sti.ti_partnum
    AND stn.dbsname = sdb.name
    GROUP BY stn.dbsname;
    MSN:jianghua1213@hotmail.com
    Beijing China
    http://www.91talk.net
    http://www.91talk.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
  •