Post reply

index space is a negative number

  • December 20, 2007 at 12:06 pm

    #120056

    Hi ,

    I have a table on sql 2000 which has 3 non-clustered indexed.

    when I use Management studio(2005) to check table property by right clicking on table and select property, I found that the index space for this table is -65.83 MB data space is 297.11 MB and there are 686943 rows

    (kind of a big table).

    My question is the space for index is a negative number, what does it mean, is it correct?

    I ran the dbcc indexdefrag, dbcc dbreindex and the index space was even negative (-85.44MB) but the performance was better.

    Also I ran dbcc showcontig both before reindex and after reindex but it gave me exactly same result/information. and it sayed

    - Extent Scan Fragmentation ...................: 78.02% (even after I ran defrag).

    This is confusing, can anybody explain?

    Thanks!

  • December 20, 2007 at 12:22 pm

    #761877

    Rebuild the index, and it should have a clustered index on it. Likely it's just bad data.

  • December 20, 2007 at 12:57 pm

    #761888

    Run sp_updateusage

    Edit: Correction, that should be DBCC UPDATEUSAGE. Sorry

    DBCC UPDATEUSAGE ('DB name','Table name');

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 20, 2007 at 1:02 pm

    #761893

    GilaMonster (12/20/2007)

    Run sp_updateusage

    THAT would be the ticket...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 21, 2007 at 6:59 am

    #762093

    Hi All,

    Thanks for all your help!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply