Take the 2-minute tour ×
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. It's 100% free, no registration required.

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? –  ElvisLikeBear Nov 7 '12 at 23:23
add comment

1 Answer

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,
            a3.name AS [schemaname],
            a2.name AS [tablename],
            a1.rows as row_count,
            (a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,
            a1.data * 8 AS data,
            (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data 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
            FROM
            (SELECT
            ps.object_id,
            SUM (
            CASE
            WHEN (ps.index_id < 2) THEN row_count
            ELSE 0
            END
            ) AS [rows],
            SUM (ps.reserved_page_count) AS reserved,
            SUM (
            CASE
            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)
            END
            ) 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
            (SELECT
            it.parent_id,
            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
            select
            -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
1  
Excellent. Fixed that line for you also :) –  ElvisLikeBear 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
1  
I just edited the post, highlighted all of the code and hit the code button (braces icon on the toolbar) :) –  ElvisLikeBear 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
add comment

Your Answer

 
discard

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.