Index size.

  • Can anyone please suggest, how to find the occupied size of an index in the disk?

    Neither dbcc updateusage nor the sp_spaceused gives the details..

    Please suggest.

    Thanks.

  • sys.dm_db_indx_physical_stats

    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
  • It is SQL 2000

    Thanks.

  • This will give you the Reserved and Used space details for all the indexes in a table. If this does not meet your requirement, just tweak the query.

    select

    name,sum(reserved)*8 as [sum_reserved (KB)], sum(used)*8 as [sum_used (KB)]

    from

    sysindexes

    where

    indid in (1, 255)

    group by

    name

    order by name

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • GilaMonster (7/31/2010)


    sys.dm_db_indx_physical_stats

    Just an FYI. There is a typo. "e" is missing in index.

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • Sourav-657741 (7/31/2010)


    It is SQL 2000

    Use DBCC SHOWCONTIG

  • Sourav-657741 (7/31/2010)


    It is SQL 2000

    Please post SQL 2000 questions in the SQL 2000 forum in future. If you post in the 2005 forums you'll get 2005 answers.

    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

Viewing 7 posts - 1 through 6 (of 6 total)

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