Sql-server – Sum of table sizes don’t match with mdf size

disk-spacesql server

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)

Disk Usage Report

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

enter image description here

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:

Shrink dialog

Best Answer

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;
Related Question