How Much Space Does My Database Use?

Since I have been spending some time cleaning up some databases in preparation for some server consolidation work, I thought I would share some useful queries for determining things like how large your database is, how full the files are, which tables and indexes use the most space, which tables might be dormant, etc.

As I have noted, some of the queries are SQL Server 2008 specific, and some can be resource intensive and time consuming. Have fun with these!

    USE YourDatabaseName;
    GO

    -- Individual File Size query for a database
    SELECT name AS [File Name] , file_id, physical_name AS [Physical Name], 
    size/128 AS [Total Size in MB],
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 
    AS [Available Space In MB]
    FROM sys.database_files;
    
    
    -- Total File Size query for a database
    SELECT SUM(size/128)AS [Total Database Space Allocated], 
    SUM(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0) 
    AS [Total Available Space In MB]
    FROM sys.database_files;
    
    -- Get space used for entire database
    EXEC sp_spaceused;
    
    -- Get space used for entire database 
    --(update usage information, may take some time, and affect server)
    EXEC sp_spaceused @updateusage = N'TRUE';
    
    
    -- Get total number of rows in a table (much less cost than SELECT COUNT(*))
    SELECT OBJECT_NAME(object_id) AS [Table Name], SUM(Rows) AS [Row Count] 
    FROM sys.partitions 
    WHERE index_id < 2 --ignore the partitions from the non-clustered index if any
    GROUP BY object_id
    ORDER BY SUM(Rows) DESC;
    
    -- Get Table names, row counts, and compression type (SQL 2008 Only)
    SELECT OBJECT_NAME(object_id) AS [Table Name], SUM(Rows) AS [Row Count], 
           data_compression_desc AS [Compression]
    FROM sys.partitions 
    WHERE index_id < 2 --ignore the partitions from the non-clustered index if any
    AND OBJECT_NAME(object_id) NOT LIKE 'sys%'
    AND OBJECT_NAME(object_id) NOT LIKE 'queue_%' 
    AND OBJECT_NAME(object_id) NOT LIKE 'filestream_tombstone%' 
    GROUP BY object_id, data_compression_desc
    ORDER BY SUM(Rows) DESC;
    
    
    -- Unused tables & indexes. 
    -- Tables have index_id’s of either 0 = Heap table or 1 = Clustered Index
    SELECT  OBJECT_NAME(i.OBJECT_ID) AS [Table Name], i.NAME AS [Index Name], i.INDEX_ID
    FROM sys.indexes AS i
    INNER JOIN sys.objects AS o
    ON i.OBJECT_ID = o.OBJECT_ID
    WHERE OBJECTPROPERTY(o.OBJECT_ID,'IsUserTable') = 1
    AND i.INDEX_ID 
    NOT IN (SELECT s.INDEX_ID
            FROM SYS.DM_DB_INDEX_USAGE_STATS AS s
            WHERE s.OBJECT_ID = i.OBJECT_ID
            AND i.INDEX_ID = s.INDEX_ID
            AND DATABASE_ID = DB_ID(db_name()))
    ORDER BY [Table Name], i.INDEX_ID, [Index Name] ASC;
    
    
    -- Index Size Used for all tables (can be very slow on a large database)
    SELECT OBJECT_NAME(P.object_id) AS [Table_Name], [name] AS [Index Name], 
           type_desc AS [Index Type], 
           (page_count * 8.0) AS [Space Used (KB)],  
           (page_count * 8.0 / 1024.0) AS [Space Used (MB)] 
    FROM sys.indexes AS I
    INNER JOIN sys.dm_db_index_physical_stats(db_id(), 
    object_id('.'), null, null, null) AS P 
    ON I.[object_id] = P.[object_id] 
    AND I.[index_id] = P.[index_id]
    ORDER BY OBJECT_NAME(P.object_id);

Technorati Tags: SQL Server 2008

About these ads