How to find where all of the space in a SQLServer database is being used

Question

We have a SQL server database which, according to Microsoft SQL Server Management Studio has just 119Mb out of 6436Mb available.

Yet the command: EXEC sp_msforeachtable 'sp_spaceused ''?'''
reveals a total reserved space that is less than 2Gb

How can we find out where the rest of the space is being used?

Answer 1

Try running this script I use on our Db's. This may give you some more info. Look out for Index space:

CREATE TABLE #temp(
    rec_id  int IDENTITY (1, 1),
    table_name varchar(128),
    nbr_of_rows int,
    data_space decimal(15,2),
    index_space decimal(15,2),
    total_size decimal(15,2),
    percent_of_db decimal(15,12),
    db_size  decimal(15,2))

    -- Get all tables, names, and sizes
    EXEC sp_msforeachtable @command1="insert into #temp(nbr_of_rows, data_space, index_space) exec sp_mstablespace '?'",
          @command2="update #temp set table_name = '?' where rec_id = (select max(rec_id) from #temp)"

    -- Set the total_size and total database size fields
    UPDATE #temp
    SET total_size = (data_space + index_space), db_size = (SELECT SUM(data_space + index_space) FROM #temp)

    -- Set the percent of the total database size
    UPDATE #temp
    SET percent_of_db = (total_size/db_size) * 100

    -- Get the data
    SELECT *
    FROM #temp
    ORDER BY nbr_of_rows DESC

    --select sum(nbr_of_rows) from #temp 
    --for xml auto

    -- Comment out the following line if you want to do further querying
    DROP TABLE #temp

Answer 2

If you are using SQL Server 2005 with Reporting Services installed, you can try running the built-in report called "Disk Usage by Table". You can access this in Management Studio by right-clicking on your database and selecting: Reports->Standard Reports->Disk Usage by Table.

This report will give you the following information: Table Name, Number of Records, Reserved (KB), Data (KB), Indexes (KB), Unused (KB)