Disk Usage by Top Tables

• ben.brugman

  SSChampion

  Points: 13369

  More actions

  October 17, 2012 at 5:59 am

  #269699

  Hello All,

  The report [font="Comic Sans MS"]Disk Usage by Top Table[/font] is very usefull.

  What is the source of the numbers in this report ?

  Or how can I get this report in a table ?

  Now I use Excel as an intermediate and do some cutting an pasting.

  Thanks for your time and attention,

  Ben Brugman

  (Did a google search on :

  Disk Usage by top tables results in a table)

  On

  I found:

  CREATE TABLE #TableSizes (name sysname, rows varchar(16),

  reserved varchar(16), data varchar(16), index_size varchar(16),unused varchar(16))

  INSERT #TableSizes

  EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

  SELECT TOP (100) * FROM #TableSizes

  ORDER BY DATA desc

  But here the numbers can not be sorted.

  (changing the declaration to bigint gives

  Msg 8114, Level 16, State 1, Procedure sp_spaceused, Line 178

  Error converting data type varchar to bigint.

  )

  Did a rebuild on that:

  -- Create a tableSizes table which displays the same information as Disk Usage by Top Tables report.

  -- Ben Brugman

  -- 20121017

  -- Part of the source comes from:

  -- http://database.ittoolbox.com/groups/technical-functional/sql-server-l/tables-sizes-in-a-db-4071428

  CREATE TABLE ##TableSizes (name sysname, rows varchar(16),

  reserved varchar(16), data varchar(16), index_size varchar(16),unused varchar(16))

  INSERT ##TableSizes

  EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

  update ##TableSizes set

  Reserved = replace(reserved,'kb',''),

  index_size = replace(reserved,'kb',''),

  data = replace(reserved,'kb',''),

  unused = replace(reserved,'kb','')

  CREATE TABLE ##TableSizes2 (name sysname, rows bigint,

  reserved bigint, data bigint, index_size bigint,unused bigint)

  insert into ##TableSizes2 select * from ##TableSizes

  SELECT TOP (1000) * FROM ##TableSizes2

  ORDER BY reserved desc

  drop table ##TableSizes

  drop table ##TableSizes2

  This is a bit cumbersome, I think that there is a more direct solution/call/script ?

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  October 17, 2012 at 6:02 am

  #1549965

  use varchar(16) instead of bigint

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

• Sean Pearce

  SSCoach

  Points: 15999

  More actions

  October 17, 2012 at 6:09 am

  #1549968

  Bhuvnesh (10/17/2012)

  ---

  use varchar(16) instead of bigint

  The OP cannot sort the result set using varchar, hence the bigint.

  

  The SQL Guy @ blogspot[/url]

  @SeanPearceSQL

  About Me[/url]

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  October 17, 2012 at 8:11 am

  #1550023

  Far easier way, just query sys.dm_db_partition_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

• zimoch2

  SSC Rookie

  Points: 33

  More actions

  November 10, 2013 at 4:39 am

  #1665336

  Hello,

  I'm newbie in interpreting of Disk Usage by Top Tables report.

  Could you give me some advice how to read it ?

  What does it mean if table with big number of data have a very small Indexes ? Could it be a problem that I have not enough indexes for all columns ?

  Example from my db:

  Table Name# RecordsReserved (KB)Data (KB)Indexes (KB)Unused (KB)

  dbo.Materiales515 011583 592258 488324 912192

  dbo.TariffsContent173 517292 18469 576222 304304

  dbo.MaterialesBase9 857139 432135 7523 536144

  dbo.LanguageContent216 112113 75270 98442 70464

  dbo.Translation251 77195 96057 08838 712160

  dbo.Dibujos75390 71290 51296104

  dbo.DescripcionCampos79 08855 04847 1527 85640

  dbo.Escandallos3 31452 95252 424360168

  dbo.ContenidoTarifario256 90752 42450 1362 2808

  dbo.ContenidoOpciones27 21243 99241 4162 440136

  Open.SetsDescriptionsOptions51 28521 44820 52089632

  dbo.Reglas33 89218 24818 08012840

• ben.brugman

  SSChampion

  Points: 13369

  More actions

  November 10, 2013 at 8:41 am

  #1665339

  This is a rather old thread, so for this question it would have been better to start a new thread.

  I use the numbers always as a rough indication;

  Table Name The tablename

  Records The number of records in the table (from internal administration).

  Reserved Total space for this table.

  Data The space used for the data.

  Indexes The space used for the indexes.

  Unused The still unused space.

  Adding up all reserved spaces from all tables should account for the size of the database.

  As said all numbers should be taken a a rough indication.

  In most circumstances the numbers are very accurate.

  Ben