July 31, 2010 at 10:25 am
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.
July 31, 2010 at 10:48 am
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
July 31, 2010 at 11:01 am
It is SQL 2000
Thanks.
July 31, 2010 at 12:06 pm
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
July 31, 2010 at 12:27 pm
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
July 31, 2010 at 12:52 pm
Sourav-657741 (7/31/2010)
It is SQL 2000
Use DBCC SHOWCONTIG
July 31, 2010 at 12:55 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply