sql-serversql-server-2005

How can you tell what Tables are taking up the most space in a SQL Server 2005 Database?


How can you tell what Tables are taking up the most space in a SQL Server 2005 Database?

I am sure there is some System Stored Procedure that shows this information.

I have a TEST database that grew from 1tb to 23tb. We are currently doing a lot of client conversion testing in the database, which entails running the same conversion Stored Procedure multiple times. It does DELETEs which I am sure is increasing the Transaction Log. But this got me thinking to ask this question.

info

the big problem is the dbo.Download table, it creates massive storage that is actually not needed, I had 3GB before truncating it, then 52MB ;)


Solution

  • Try this script - it will list the number of rows and the space used by data rows (and the total space used) for all tables in your database:

    SELECT 
     t.NAME AS TableName,
     i.name AS indexName,
     SUM(p.rows) AS RowCounts,
     SUM(a.total_pages) AS TotalPages, 
     SUM(a.used_pages) AS UsedPages, 
     SUM(a.data_pages) AS DataPages,
     (SUM(a.total_pages) * 8) / 1024 AS TotalSpaceMB, 
     (SUM(a.used_pages) * 8) / 1024 AS UsedSpaceMB, 
     (SUM(a.data_pages) * 8) / 1024 AS DataSpaceMB
    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
    WHERE 
     t.NAME NOT LIKE 'dt%' AND
     i.OBJECT_ID > 255 AND  
     i.index_id <= 1
    GROUP BY 
     t.NAME, i.object_id, i.index_id, i.name 
    ORDER BY 
     OBJECT_NAME(i.object_id)