Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2008
    Posts
    5

    Unanswered: How to calculate db space used in ASE15 with 8K page size?

    For an ASE15 server with an 8K page size, does anyone have sql to calculate the amount of data space used in a database?

    Thanks.
    Steve

  2. #2
    Join Date
    Feb 2007
    Location
    India
    Posts
    56

    Hi

    You can get it from sp_helpsegment 'default' in that database. At last row you can find Total pages and free pages.
    Regards,
    Naveen.
    Sybase DBA-Consultant

  3. #3
    Join Date
    Mar 2008
    Posts
    96
    try the following query

    select db_name(dbid),sum(size/512) "total size",sum((curunreservedpgs(dbid,lstart,unreserved pgs)/128)) "used size" from sysusages group by dbid



    all the size will be in MBs

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    run sp_helpdb
    e.g.
    Code:
    1> sp_helpdb mydb
    2> go
     name db_size       owner dbid created      status                                                                                
     ---- ------------- ----- ---- ------------ ------------------------------------------------------------------------------------- 
     mydb      700.0 MB sa       4 Nov 16, 2007 select into/bulkcopy/pllsort, trunc log on chkpt, ddl in tran, abort tran on log full 
    
    (1 row affected)
     device_fragments               size          usage                created                   free kbytes      
     ------------------------------ ------------- -------------------- ------------------------- ---------------- 
     mydb_d1                             500.0 MB data only            Nov 16 2007 10:16AM                 505124 
     mydb_l1                             200.0 MB log only             Nov 16 2007 10:16AM       not applicable   
                                                                    
     -------------------------------------------------------------- 
     log only free kbytes = 203996                                  

  5. #5
    Join Date
    Mar 2007
    Posts
    86
    Quote Originally Posted by Steve2
    For an ASE15 server with an 8K page size, does anyone have sql to calculate the amount of data space used in a database?

    Thanks.
    Steve

    sure .. just replace << DB NAME >> with your db name and let it rip ..
    WIll calculate based on the page size .. this took a while to figure out so treat it kindly .. ROb Verchoor is adding it to his book (but he would not give me a free copy for this!!) :-))

    select substring (S.name,1,20) as segment_name,
    sum (U.size) / (512 / (@@maxpagesize / 2048)) as total_size,
    sum(curunreservedpgs(db_id(), U.lstart, 0)) / (512 / (@@maxpagesize / 2048)) as free_space_MB,
    100* ( sum(curunreservedpgs(db_id(), U.lstart, 0)) / (512 / (@@maxpagesize / 2048)) ) / ( sum (U.size) / (512 / (@@maxpagesize / 2048)) )
    as free_space_PCT,
    convert (char(10), getdate(),111) as 'date',
    convert (varchar(8),getdate(),8) as 'time'
    from << DB NAME >>..syssegments S,
    master..sysusages U,
    master..sysdevices D
    where dbid = db_id()
    and U.vdevno = D.vdevno
    and vstart between low and high
    and power (2,S.segment) & U.segmap > 0
    -- to delimit by a specific segment name
    --and substring (S.name,1,20) = '<<segment name>>'
    group by S.name
    order by S.name

Posting Permissions

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