Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2012
    Posts
    133

    Unanswered: how to resolve negative value on used pages

    hi guys,
    how do you resolve negative values for used pages on syslogs. I've run some dbcc on syslogs system table unfortunately it does not fix the problem, restarting ASE and problem still persist. anyone has good recommendation on how to solve this kind of problem. much appreciated with your help. thanks

  2. #2
    Join Date
    Aug 2002
    Location
    Madrid, Spain
    Posts
    97
    All possibilities have some risks, and some high risks.
    First of all, why did it happen? Although it is possible that some accident could drive to this inconsistency, often it is the consequence of adventuring with risky administrative actions. If this is the case, remember it and avoid doing it again.
    If this is a production database, I would suggest dumping and loading into a toy server and experiment there. But I must warn that some of this solutions have some degree of hazard, and they could run OK on your test server but make a mess in the production one.
    As far as I remember, dbcc usedextents was among the less risky. Even though, better place the database in "single user" before running the fix version of any dbcc command. We used to run these on version 12.5:
    dbcc usedextents(DATABASE,0,1)
    dbcc usedextents(DATABASE,1,1)
    where DATABASE is either the database name or its id (a number)

    Most dbcc commands don't output messages unless traceflag 3604 is on, so set it on before running any dbcc:
    dbcc traceon(3604)
    or
    set switch on 3604 -- if the ASE version admits it

    That used to correct some free space inconsistencies, but I doubt it will correct syslogs problems. Fixing the log with tablealloc is riskier. Better get a database dump in case you'll have to restore the database. Again in single user, execute
    use DATABASE
    go
    dbcc traceon(3604)
    go
    dbcc tablealloc(8, 'full', 'fix')
    go

    Make some checks while in "single user". I cannot remember whether is advisable to restart ASE now. And don´t forget to set single user off when done.

    Years since last time I have to deal with this kind of problems. Please do some google before.

    Regards,
    Mariano Corral Herranz

  3. #3
    Join Date
    Jun 2012
    Posts
    18
    If DBCC TABLEALLOC(syslogs, full, fix) doesn't work, you can try with "dbcc dbrepair(base_name, 'fixlogfreespace')".
    Sybase will recalculate everything.

  4. #4
    Join Date
    Feb 2012
    Posts
    133
    hi @acherrytells,
    is the command dbcc dbrepair(base_name ,fixlogfreespace) can be run during production? btw, i'm running ase ver 12.5.3 also would there be any alternative select statement to calculate just the "used pages" for the logsegment.

    TIA

  5. #5
    Join Date
    Aug 2002
    Location
    Madrid, Spain
    Posts
    97
    If internal space info is accurate and all what it is needed is to query the used log space, the functions used by sp_helpdb are fast and harmless. The free pages are queried by
    select lct_admin("logsegment_freepages", @database_id )
    But these "free" pages still include pages reserved for rollbacks (something called "compensating log records", massively used by large deletes), which in fact are not free. What sp_helpdb does is to subtract them:
    select lct_admin("logsegment_freepages", @database_id ) -
    lct_admin("reserved_for_rollbacks", @database_id )

    Perhaps you prefer to consider the last chance threshold as unusable space, since ASE will be in a log-full situation once the LCT has been reached. That is, you may wish to subtract this too. The LCT space in pages is selected by this query, which must be run from the database whose LCT is queried (the two above can be run from any database)
    select lct_admin("reserve", 0 )

    Of course, all of them may obtain erroneous results while the corruption in your syslogs persists. They only work in healthy databases.

    Used space is total space minus free. Total space used to be queried from sysusages. To be really reliable, syslogs should be assigned to dedicated devices, which is one of the most known best practices in ASE administration.

    Regards,
    Mariano Corral Herranz

  6. #6
    Join Date
    Jun 2012
    Posts
    18

    Post

    Quote Originally Posted by makecompile View Post
    hi @acherrytells,
    is the command dbcc dbrepair(base_name ,fixlogfreespace) can be run during production? btw, i'm running ase ver 12.5.3 also would there be any alternative select statement to calculate just the "used pages" for the logsegment.

    TIA
    When I have a database with weird log numbers, I audit with these three checks :
    Code:
    sp_helpdb base_name -- give informations on database
    go
    
    use base_name
    go
    sp_helpsegment logsegment -- give informations on segment
    go
    
    select lct_admin('logsegment_freepages',db_id()) as 'free_pages', 
    lct_admin('reserved_for_rollbacks',db_id()) as 'reserved_pages',
    lct_admin('num_logpages',db_id()) as 'num_used_pages'
    go -- number pages

  7. #7
    Join Date
    Feb 2012
    Posts
    133
    thanks so much guys..is there also select lct_admin for data pages similar to sp_helpsegment logsegment but this time it is for default segment..

  8. #8
    Join Date
    Jun 2012
    Posts
    18
    lct_admin is about LCT. No link with default segment.

    About sp_helpsegment, the commande 'sp_helpsegment' give u all names of present segments.
    So do 'sp_helpsegment "default"'

  9. #9
    Join Date
    Feb 2012
    Posts
    133
    got it thanks for the idea @acherrytells

Posting Permissions

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