Disk Usage by Top Tables

  • 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 ?

  • use varchar(16) instead of bigint

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

  • 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]

  • 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
  • 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

  • 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

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

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