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

    Unanswered: need to calculate percent of log used (ASE 15)

    For ASE15, does anyone have a proc that calculates the percent used for a log?

    Thanks.
    Steve

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Code:
    select rundate,dbname
    , pctdata=convert(decimal(5,2),100-DataFree/DataSize*100)
    , pctlog=convert(decimal(5,2),100-LogFree/LogSize*100) 
    , LogsegOnDataseg 
    from(
    select 'rundate'=convert(char(10),getdate(),111)+' '+convert(char(8),getdate(),8)
    ,'dbname'=db_name(t1.dbid)
    ,'DataSize'=convert(int,sum
      (case when t1.segmap!=4 
       then t1.size/t2.dbpgsmb 
       else null end))
    ,'DataFree'=convert(decimal(11,1),sum
      (case when t1.segmap!=4 
       then curunreservedpgs(t1.dbid,t1.lstart, t1.unreservedpgs)/t2.dbpgsmb 
       else null end))
    ,'LogSize'=convert(decimal(11,0),sum
      (case when t1.segmap&4=4 
       then t1.size/t2.dbpgsmb end))
    ,'LogFree'=convert(decimal(11,1),
      ( lct_admin('logsegment_freepages'  , t1.dbid)
       -lct_admin('reserved_for_rollbacks', t1.dbid)
      )/t2.dbpgsmb)
    ,'LogsegOnDataseg'=min
      (case when t1.segmap!=4 and t1.segmap&4=4
       then '*'
       else '' end) 
    from master..sysusages t1 
    ,(select 'dbpgsmb' = (1048576. / v.low)
      from master.dbo.spt_values v
      where v.number = 1
        and v.type = 'E'
     )t2
    group by t1.dbid
    )t3

Posting Permissions

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