Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a database that grew very significantly overnight and I'd like to find out why. I've restored yesterdays backup to another location. Is there a query I can run to query the size of tables or indexes so I can compare the two?

share|improve this question
If you don't have many tables, you can use the Disk Usage by Top Tables report in Management Studio. – Jon Seigel Nov 7 '12 at 23:12
@Jon unfortunately I don't have access to that. If there a way to reproduce these results via query? – Codingo Nov 7 '12 at 23:23
up vote 4 down vote accepted

I profiled the query and this is the portion that returns the 'Disk Usage by Top Tables' results:

exec sp_executesql @stmt=N'begin try 

            SELECT TOP 1000
            (row_number() over(order by (a1.reserved + ISNULL(a4.reserved,0)) desc))%2 as l1,
   AS [schemaname],
   AS [tablename],
            a1.rows as row_count,
            (a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,
   * 8 AS data,
            (CASE WHEN (a1.used + ISNULL(a4.used,0)) > THEN (a1.used + ISNULL(a4.used,0)) - ELSE 0 END) * 8 AS index_size,
            (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused
            SUM (
            WHEN (ps.index_id < 2) THEN row_count
            ELSE 0
            ) AS [rows],
            SUM (ps.reserved_page_count) AS reserved,
            SUM (
            WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
            ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
            ) AS data,
            SUM (ps.used_page_count) AS used
            FROM sys.dm_db_partition_stats ps
            GROUP BY ps.object_id) AS a1
            LEFT OUTER JOIN
            SUM(ps.reserved_page_count) AS reserved,
            SUM(ps.used_page_count) AS used
            FROM sys.dm_db_partition_stats ps
            INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
            WHERE it.internal_type IN (202,204)
            GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
            INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id )
            INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
            WHERE a2.type <> N''S'' and a2.type <> N''IT''
            end try
            begin catch
            -100 as l1
            ,   1 as schemaname
            ,       ERROR_NUMBER() as tablename
            ,       ERROR_SEVERITY() as row_count
            ,       ERROR_STATE() as reserved
            ,       ERROR_MESSAGE() as data
            ,       1 as index_size
            ,       1 as unused
            end catch',@params=N''
share|improve this answer
how do I modify the post so that the "sp_executesql @stmt=N'begin try" is part of the query? – Ali Razeghi Nov 7 '12 at 23:32
Excellent. Fixed that line for you also :) – Codingo Nov 7 '12 at 23:40
Thanks! Do you know where I can find how to fix them in the future? :) – Ali Razeghi Nov 8 '12 at 2:31
I just edited the post, highlighted all of the code and hit the code button (braces icon on the toolbar) :) – Codingo Nov 8 '12 at 2:33
Thanks again dude, i thought I had to wrap it around, not highlight. :) – Ali Razeghi Nov 8 '12 at 15:33

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.