Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    23

    Unanswered: negative log % usage

    I have a stored procedure (inherited) that is checking how much of the data devices and log devices are being used. When it runs on one particular db, the log usage percentage comes back to -83%. When looking at the sysusages I notice the unreservedpgs is higher than the size. Is this possible? How does this happen and how do you change it?

    Sorry for the possibly stupid question, but I am fairly new to dbs and Sybase in particlular.

    dbid segmap lstart size vstart pad unreservedpgs
    ------ ----------- ----------- ----------- ----------- ------ -------------
    11 3 0 5120000 738197504 NULL 1694018
    11 3 5120000 5120000 234881024 NULL 2158086
    11 4 10240000 2560000 251658240 NULL 4690972

    The full Stored Procedure:

    create procedure sp_dbinfo
    @dbname varchar(30) /* database name to change */
    as
    /*
    if @@trancount = 0
    begin
    set chained off
    end

    set transaction isolation level 1

    set nocount on
    */

    create table spdbfree (
    Segment_name char(33) null,
    Size int null,
    Usage char(20) null,
    Free_space int null )

    /*
    ** Initialize spdbfree from sysdatabases
    */

    insert into spdbfree (Segment_name,Size,Usage,Free_space)
    select v.name,
    round((a.low * convert(float, u.size)) / 1048576, 1) * 1024 ,
    convert(char(20), m.description),
    curunreservedpgs(db_id(@dbname),
    u.lstart,
    u.unreservedpgs) * 2 --/1024
    from master.dbo.sysdatabases d,
    master.dbo.sysusages u,
    master.dbo.sysdevices v,
    master.dbo.spt_values a,
    master.dbo.spt_values b,
    master.dbo.sysmessages m
    where d.dbid = u.dbid
    and v.low <= u.size + vstart
    and v.high >= u.size + vstart - 1
    and v.status & 2 = 2
    and d.name = @dbname and a.type = "E"
    and a.number = 1
    and b.type = "S"
    and u.segmap & 7 = b.number
    and b.msgnum = m.error
    -- and isnull(m.langid, 0) = @sptlang
    order by 1

    --select * from spdbfree

    declare @Total_Data int,
    @Total_log int,
    @Tot_free_data int,
    @Tot_free_log int,
    @UsedData int,
    @UsedLog int,
    @UsedData_per int,
    @UsedLog_per int,
    @dataUnit1 char(2),
    @dataUnit2 char(2),
    @dataUnit3 char(2),
    @logUnit1 char(2),
    @logUnit2 char(2),
    @logUnit3 char(2)

    select @Total_Data = sum(Size)
    from spdbfree
    where Usage = 'data only' or Usage = 'data and log'

    select @Total_log = sum(Size)
    from spdbfree
    where Usage = 'log only' or Usage = 'data and log'

    select @Tot_free_data = sum(Free_space)
    from spdbfree
    where Usage = 'data only' or Usage = 'data and log'

    select @Tot_free_log = sum(Free_space)
    from spdbfree
    where Usage = 'log only' or Usage = 'data and log'

    select @UsedData = @Total_Data - @Tot_free_data
    select @UsedLog = @Total_log - @Tot_free_log
    select @UsedData_per = ceiling(convert(float,@UsedData) / @Total_Data * 100)
    select @UsedLog_per = ceiling(convert(float,@UsedLog) / @Total_log * 100)


    if @Total_Data >= 1024
    begin
    select @Total_Data = @Total_Data /1024
    select @dataUnit1 = "MB"
    end
    else
    select @dataUnit1 = "KB"

    if @Tot_free_data >= 1024
    begin
    select @Tot_free_data = @Tot_free_data /1024
    select @dataUnit3= "MB"
    end
    else
    select @dataUnit3= "KB"

    if @UsedData >= 1024
    begin
    select @UsedData = @UsedData /1024
    select @dataUnit2= "MB"
    end
    else
    select @dataUnit2= "KB"

    if @Total_log >= 1024
    begin
    select @Total_log = @Total_log /1024
    select @logUnit1 = "MB"
    end
    else
    select @logUnit1 = "KB"

    if @Tot_free_log >= 1024
    begin
    select @Tot_free_log = @Tot_free_log /1024
    select @logUnit3 = "MB"
    end
    else
    select @logUnit3 = "KB"

    if @UsedLog >= 1024
    begin
    select @UsedLog = @UsedLog /1024
    select @logUnit2= "MB"
    end
    else
    select @logUnit2= "KB"

    create table datatmp(
    DS_NAME varchar (20),
    DB_NAME varchar (20),
    DATA_SIZE varchar (20),
    DATA_USED varchar (20),
    DATA_FREE varchar (20),
    DATA_USAGE varchar (20),
    LOG_SIZE varchar (20),
    LOG_USED varchar (20),
    LOG_FREE varchar (20),
    LOG_USAGE varchar (20))

    insert into datatmp
    values (isnull(@@servername,"Unknown") ,
    convert(varchar,@dbname) ,
    convert(varchar,@Total_Data) +@dataUnit1 ,
    convert(varchar,@UsedData) + @dataUnit2 ,
    convert(varchar,@Tot_free_data) + @dataUnit3 ,
    convert(varchar,@UsedData_per) + '%' ,
    convert(varchar,@Total_log) + @logUnit1 ,
    convert(varchar,@UsedLog) + @logUnit2 ,
    convert(varchar,@Tot_free_log) + @logUnit3 ,
    convert(varchar,@UsedLog_per) + '%')

    select datatmp.DS_NAME, datatmp.DB_NAME, datatmp.DATA_SIZE, datatmp.DATA_USED, datatmp.DATA_FREE, datatmp.DATA_USAGE, datatmp.LOG_SIZE, datatmp.LOG_USED, datatmp.LOG_FREE, datatmp.LOG_USAGE from datatmp

    drop table #datatmp
    drop table #spdbfree
    Last edited by bgame; 12-02-03 at 20:07.

  2. #2
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    Develop your own tool, starting with
    Code:
    select db_name(u.dbid) DB , g.name,
             convert(varchar(15), sum(curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs)/512)) FREE,
            convert(varchar(15), sum(u.size/512)) SEG_SIZE
    from master..sysusages u, master..syssegments g
    where  (power(2,g.segment) & u.segmap) <> 0
    GROUP BY u.dbid, g.name
    Be carefull : each device using several segments will appear several times.

    EG for my master database (35 Mb, mixed default/log/system)

    Code:
    DB                             name                           FREE            SEG_SIZE
    ------------------------------ ------------------------------ --------------- -------------
    master                         default                        29              35
    master                         indexsegment              29              35
    master                         logsegment                  29              35
    master                         system                        29              35

  3. #3
    Join Date
    Nov 2003
    Posts
    23
    OK, but how can the unreservedpgs be higher than the defined log size? Also, how can you alleviate this? Does anyone know how to tell Sybase to recalculate this number?

    Also, what causes this to happen?

  4. #4
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    Originally posted by bgame
    Does anyone know how to tell Sybase to recalculate this number?
    Yes, you can force ASE to re-calculate this using usedextents command options.

    I can't find any documentation on this available on the Sybase site. I'd contact Sybase Support centre on impacts of running this and also for the syntax.

Posting Permissions

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