8

I have a SQL Server 2008 R2 database being used by several deployed programs.

Question: Is there an easy way to display how much space each table consumes, for all of the tables in the database, and distinguish logical space from disk space?

If I use SSMS (Management Studio), the storage properties shown for the database reads 167 MB with 3 MB "available" (about the right size, but I'm concerned about the 3 MB available - is this a limit to be concerned about, assuming I know I have enough disk space?)

I can drill into each table, but that takes forever to do.

I know I can write my own queries and test around, but I'd like to know if there's already an easy (built-in?) way to do this.

13

In SSMS, right-click on the database and go to "Reports", "Standard Reports", "Disk Usage by Table". It will tell you the total size, the data size, the index size, and the unused size for each table (as well as the row count).

  • 1
    I hadn't seen that before and I like it better than the script - except that on some databases it fails because I don't have "VIEW DATABASE STATE" permission - but the script will work in that case. Go figure! – Chris Woodruff Jul 3 at 17:30
8

It's been answered on Stack Overflow:

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name
5

The query linked to, and copied by, @Nelson is inaccurate: it ignores Indexed Views, Full Text Indexes, XML Indexes, etc.

If you want a query that will include everything without executing sp_spaceused via sp_MSForEachTable, then I have already posted two variations of it (one here on DBA.StackExchange and the other on StackOverflow) so I won't copy them here:

4

Just for fun, here's a query that will generate the same data as the report in nateirvin's answer

create table #disk_usage
(
    name varchar(128)
    ,rows varchar(20)
    ,reserved varchar(20)
    ,data varchar(20)
    ,index_size varchar(20)
    ,unused varchar(20)
);

exec sp_msforeachtable 'insert into #disk_usage exec sp_spaceused [?]'

select SCHEMA_NAME(st.schema_id) + '.' + du.name 'Table Name'
 ,du.rows '# Records'
 ,du.reserved 'Reserved (KB)'
 ,du.data 'Data (KB)'
 ,du.index_size 'Indexes (KB)'
 ,du.unused 'Unused (KB)'
 from #disk_usage du
left join sys.tables st
on du.name = st.name
order by cast(left(reserved, len(reserved) - 3) as bigint) desc;

drop table #disk_usage

Okay, because I really hate myself, I wrote a query that will generate the results of the report, format it as an HTML table, and send it as an email. Matching up the report background colors is left as an exercise for the reader.

declare @subject nvarchar(25) = 'Disk Usage by Top Tables';

declare @recipients nvarchar(25) = 'mailbox@example.com';

create table #disk_usage
(
    name varchar(128)
    ,rows varchar(20)
    ,reserved varchar(20)
    ,data varchar(20)
    ,index_size varchar(20)
    ,unused varchar(20)
);

exec sp_msforeachtable 'insert into #disk_usage EXEC sp_spaceused [?]'

declare @body nvarchar(max) = 
'<table cellspacing="0">
    <thead>
        <tr>
            <th>Table Name</th>
            <th># Rows</th>
            <th>Reserved</th>
            <th>Data</th>
            <th>Indexes</th>
            <th>Unused</th>
        </tr>
    </thead>
';

set @body = @body + cast (
    (select '<td style="border: 1px solid black; padding: 2px">' + SCHEMA_NAME(s.schema_id) + '.' + t.name + '</td>'
     ,'<td style="border: 1px solid black; padding: 2px">' + rtrim(ltrim(t.rows)) + ' Rows </td>' -- for some reason this was generating a bunch of extra white space and I'm not going to bother to figure out why
     ,'<td style="border: 1px solid black; padding: 2px">' + t.reserved + '</td>'
     ,'<td style="border: 1px solid black; padding: 2px">' + t.data + '</td>'
     ,'<td style="border: 1px solid black; padding: 2px">' + t.index_size + '</td>'
     ,'<td style="border: 1px solid black; padding: 2px">' + t.unused + '</td>'
     from #disk_usage t
    left join sys.tables s
    on t.name = s.name
    order by cast(left(reserved, len(reserved) - 3) as bigint) desc
    for xml path ('tr'))
as nvarchar(max));

set @body = replace(replace(@body, '&lt;', '<'), '&gt;', '>')
set @body = @body + '</table>'

exec msdb.dbo.sp_send_dbmail 
@profile_name='A Database Mail Profile On The Target Server', 
@recipients=@recipients, 
@subject=@subject,
@body=@body,
@body_format='HTML'

drop table #disk_usage
  • 1
    Wow! That last bit of self-loathing-fueled work is wonderful (for others ;-) )! Thanks for adding that. I regret that I have but one upvote to give to this answer... – Dronz Mar 19 at 17:06
  • 1
    :) I figured if I had to go through the pain of doing it, the least I can do is save someone else from the same fate. – MikeTheLiar Mar 19 at 17:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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