Sum of table sizes don't match with mdf size

Question

I just ran a disk usage report on my database and found it's grown a lot over the past month and is currently using 3.98 GB on data (it was around 300 MB on last month)

However, when I run the disk usage by tables report I see that the space usage from the tables are currently very far from 3.98 GB

How can I find what is using the remaining space so that it adds up to 3.98 GB?

EDIT

This is the screenshot for the Shrink file dialog:

Answer 1

There are "internal" tables, queues, etc that don't show up in certain reports / queries as they are system objects. If you run the query below, which counts the number of actual pages defined in the data files in the current Database, you should see that one of the "Reserved" fields (match the appropriate "KB", "MB", or "GB" field to your report) matches the "Space Used" value in the Data File portion of that report.

;WITH cte AS
(
  SELECT COUNT(*) AS [Reserved],
         SUM(CASE WHEN pa.[is_allocated] = 1 THEN 1 ELSE 0 END) AS [Allocated]
  FROM sys.dm_db_database_page_allocations(DB_ID(), NULL, NULL, NULL, DEFAULT) pa
)
SELECT  [Reserved] AS [ReservedPages],
        [Allocated] AS [AllocatedPages],
        '---' AS [---],
        ([Reserved] * 8) AS [ReservedKB],
        ([Allocated] * 8) AS [AllocatedKB],
        '---' AS [---],
        ([Reserved] * 8) / 1024.0 AS [ReservedMB],
        ([Allocated] * 8) / 1024.0 AS [AllocatedMB],
        '---' AS [---],
        ([Reserved] * 8) / 1024.0 / 1024.0 AS [ReservedGB],
        ([Allocated] * 8) / 1024.0 / 1024.0 AS [AllocatedGB]
FROM    cte;

And below is the query to get the per-object details along with a grand total line at the top:

SELECT sch.[name], obj.[name], ISNULL(obj.[type_desc], N'TOTAL:') AS [type_desc],
       COUNT(*) AS [ReservedPages],
       (COUNT(*) * 8) AS [ReservedKB],
       (COUNT(*) * 8) / 1024.0 AS [ReservedMB],
       (COUNT(*) * 8) / 1024.0 / 1024.0 AS [ReservedGB]
FROM sys.dm_db_database_page_allocations(DB_ID(), NULL, NULL, NULL, DEFAULT) pa
INNER JOIN sys.all_objects obj
        ON obj.[object_id] = pa.[object_id]
INNER JOIN sys.schemas sch
        ON sch.[schema_id] = obj.[schema_id]
GROUP BY GROUPING SETS ((sch.[name], obj.[name], obj.[type_desc]), ())
ORDER BY [ReservedPages] DESC;

Answer 2

My comment was to large so i add it as an answer even though its not an answer.

Can you run this query within the database and add the information. I have seen that the reports sometime missmatches the accual value.

SELECT 
    t.NAME AS TableName,    
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 / 1024 AS TotalSpaceMB, 
    SUM(a.used_pages)  * 8 / 1024 AS UsedSpaceMB , 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024 AS UnusedSpaceMB
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.is_ms_shipped = 0
--    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    UsedSpaceMB DESC, t.Name

Answer 3

I'd analyse this with Exec sp_spaceused and will also check the current size of the log dbcc sqlperf (logspace)