Skip to Content

sp_spaceused negative count display

Hello,

Running ASE 15.7 SP60 on HP.UX. When executing sp_spaceused, we get a negative count displayed in "reserved" and "data" columns:

1> sp_spaceused

2> go

database_name database_size

------------- -------------

comex 111680.0 MB

(1 row affected)

reserved data index_size unused

------------- ------------- ----------- ---------

-817604568 KB -835007160 KB 16632312 KB 749720 KB

(return status = 0)

I recall that there were some dbcc's (which I don't have now) to update sysusages.

Which would be the correct steps to fix this situation?

note: sp_helpdb reports space OK

Thank you

Jose

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Nov 17, 2016 at 03:36 PM

    Generally speaking I'd grab a copy of the source code for sp_spaceused and work back from the section that generates the output to see where the negative numbers are being introduced, eg:

    * I'm running ASE 15.7 SP138

    * the 'reserved' column is the sum of @reserved_pgs + @sl_used_pages_wo_APs

    * @reserved_pgs is derived from the output of 'select spaceusage(@dbid)'

    * @sl_used_pages_wo_APS is returned from a call to sp_spaceused_syslogs

    * one of these 2 values (@reserved_pgs, @sl_used_pages_wo_APS) should be a large negative number which in turn (hopefully) gives us an idea on how to address the issue

    So, what does the following generate:

    use comex
    go
    select spaceusage(db_id())
    go
    declare @tp bigint, @fp bigint, @up bigint, @upwoAPS bigint, @cp bigint
    exec sp_spaceused_syslogs @tp out, @fp out, @up out, @upwoAPS out, @cp out
    select @tp, @fp, @up, @upwoAPS, @cp
    go

    --------------

    If the negative numbers are being returned from sp_spaceused_syslogs then I'd guess you've got an issue with corrupted free space accounting for the log.

    Over the years (and various ASE versions) there have been several ways to fix free space accounting issues. With ASE 15.7 I've found the following works well:

    use comex
    go
    dbcc traceon(3604)
    go
    dbcc dbrepair('comex','fixlogfreespace','scanlogchain',1)
    go
    dbcc traceoff(3604)
    go
    Add comment
    10|10000 characters needed characters exceeded

  • Nov 17, 2016 at 07:13 PM

    Mark,

    Thank you. Where can I get sp_spaceused_syslogs..?

    Regards

    Jose

    ,

    Thanks Mark,

    but where do I get sp_spaceused_syslosgs ?

    Jose

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 17, 2016 at 07:22 PM

    Generally speaking you'd find sp_spaceused_syslogs in the sybsystemprocs database (or installmaster script).

    Keep in mind ...

    * I don't have access to a ASE 15.7 SP60 instance

    * my response was based on the steps I used (for ASE 15.7 SP138) to figure out where the negative numbers could be coming from

    ... net result is that if ASE 15.7 SP60 doesn't have sp_spaceused_syslogs (eg, SAP has modified sp_spaceused between SP60 and SP138) then I'd suggest you follow the same steps I outlined, ie, pull up the source code for sp_spaceused and see where it's getting its numbers from.

    ---------

    Alternatively, if you *are* dealing with a free space accounting issue with syslogs, said issue should also show up in the output of:

    exec comex..sp_helpsegment logsegment

    Add comment
    10|10000 characters needed characters exceeded