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:

This entry was posted in SQL Server 2008. Bookmark the permalink.

32 Responses to How Much Space Does My Database Use?

  1. Unknown says:

    http://www.batteryfast.com/hp/nc6120.htm hp nc6120 battery http://www.batteryfast.com/dell/inspiron-1100-series.htm dell inspiron 1100 series battery http://www.batteryfast.com/dell/inspiron-1150.htm dell inspiron 1150 battery http://www.batteryfast.com/dell/inspiron-5150.htm dell inspiron 5150 battery http://www.batteryfast.com/dell/inspiron-5160.htm dell inspiron 5160 battery http://www.batteryfast.com/dell/inspiron-5100.htm dell inspiron 5100 battery http://www.batteryfast.com/dell/latitude-131l.htm dell latitude 131l battery http://www.batteryfast.com/dell/vostro-1000.htm dell vostro 1000 battery http://www.batteryfast.com/dell/gd761.htm dell gd761 battery http://www.batteryfast.com/dell/nt379.htm dell nt379 battery http://www.batteryfast.com/dell/latitude-d531.htm dell latitude d531 battery http://www.batteryfast.com/dell/precision-m65.htm dell precision m65 battery http://www.batteryfast.com/dell/vostro-1700.htm dell vostro 1700 battery http://www.batteryfast.com/gateway/8msb.htm gateway 8msb battery http://www.batteryfast.com/hp/pb992a.htm hp pb992a battery http://www.batteryfast.com/hp/dv2100.htm hp dv2100 battery http://www.batteryfast.com/hp/dv2200.htm hp dv2200 battery http://www.batteryfast.com/hp/hstnn-c29c.htm hp hstnn-c29c battery http://www.batteryfast.com/hp/nc8000.htm hp nc8000 battery http://www.batteryfast.com/dell/inspiron-b120.htm dell inspiron b120 battery http://www.batteryfast.com/dell/inspiron-b130.htm dell inspiron b130 battery http://www.batteryfast.com/gateway/12msbg.htm gateway 12msbg battery http://www.batteryfast.com/hp/7400.htm hp 7400 battery http://www.batteryfast.com/dell/inspiron-b120.htm dell inspiron b120 battery http://www.batteryfast.com/toshiba/pa3356u.htm toshiba pa3356u battery http://www.batteryfast.com/hp/2510p.htm hp 2510p battery http://www.batteryfast.com/hp/f3172a.htm hp f3172a battery http://www.batteryfast.com/dell/inspiron-2200.htm dell inspiron 2200 battery http://www.batteryfast.com/gateway/squ-414.htm gateway squ-414 battery http://www.batteryfast.com/hp/hstnn-lb33.htm hp hstnn-lb33 battery http://www.batteryfast.com/mitac/bp-8089.htm mitac bp-8089 battery http://www.batteryfast.com/toshiba/satellite-m55.htm toshiba satellite m55 battery http://www.batteryfast.com/toshiba/satellite-m115.htm toshiba satellite m115 battery http://www.batteryfast.com/toshiba/qosmio-f25.htm toshiba qosmio f25 battery http://www.batteryfast.com/toshiba/satellite-m60.htm toshiba satellite m60 battery http://www.batteryfast.com/toshiba/satellite-p200.htm toshiba satellite p200 battery http://www.batteryfast.com/toshiba/pa3536u-1brs.htm toshiba pa3536u-1brs battery http://www.batteryfast.com/hp/hstnn-db17.htm hp hstnn-db17 battery

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s