Index Space sp_spaceused versus sysindexes dpages different results

• Hartman_Ralph

  SSC Enthusiast

  Points: 103

  More actions

  July 28, 2011 at 10:30 am

  #240751

  SORRY FOR THE DOUBLE POST

  Thanks in advance for the help. I am collecting index size data in two methods. Both give different answers. Which one is correct or are they both correct.

  [SP_SpaceUsed]

  Execute sp_spaceused 'myTable' I get the Index value of "12146400 KB" (11.5GB)

  [SysIndexes]

  SELECT IndexName = name,

  IndexSizeGB = CEILING(1.0 * dpages / 128) / 1024.00,

  IndexSizeKB = (dpages * 8)

  FROM sysindexes

  WHERE NAME LIKE 'myTable_index_%'

  IndexName IndexSizeGBIndexSizeKB

  Index1 11.589843712152488

  Index2 11.367187511919288

  as you can see I get TWO Indexes for the same table above. But the SUM of the two indexes is about double that of what is given by Sp_SpaceUsed

  Please help

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  July 28, 2011 at 10:36 am

  #1360313

  Run DBCC updateusage() first. Then both should be correct.

  2K5 gives you much better tools to report on this if that can help you make the move!

  P.S. You can edit out the text on your double post and leave "double post" in it. that way peope won't waste time in the other thread.

• Hartman_Ralph

  SSC Enthusiast

  Points: 103

  More actions

  July 28, 2011 at 6:32 pm

  #1360516

  Thanks for the quick response I will give it a try. The server in question is 2008 R2 Enterprise.