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?


This is the screenshot for the Shrink file dialog:

Shrink dialog

  • Is Service Broker enabled for this database?
    – sepupic
    Commented Jun 7, 2017 at 13:40
  • Great question, do you have change tracking enable on the database? Commented Jun 7, 2017 at 14:06
  • 1
    Yes, service broker is enabled Commented Jun 7, 2017 at 14:07
  • 1
    I ran 'SELECT * FROM sys.change_tracking_databases' and it returned no rows, so I assume change tracking is not enabled Commented Jun 7, 2017 at 14:10
  • How does it look like in the Shrink File (File and not database) dialog. Can you post a screenshot of that? Commented Jun 7, 2017 at 14:30

3 Answers 3


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;
  • 2
    the second query found me the sys.sysdercv table, which is a service broker related table that is reported to grow in some situations and currently occupies 3.15 GB on my database, so now the numbers add up to the 3.98 GB from the report Commented Jun 7, 2017 at 15:26
  • @EduardoWada Great. Please see the update I just made to that per-object query. Minor change, but it now includes a grand total summary line at the top :-) Commented Jun 7, 2017 at 15:38

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.

    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
    sys.tables t
    sys.indexes i ON t.OBJECT_ID = i.object_id
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    sys.allocation_units a ON p.partition_id = a.container_id
    sys.schemas s ON t.schema_id = s.schema_id
--    t.is_ms_shipped = 0
--    AND i.OBJECT_ID > 255 
    t.Name, s.Name, p.Rows
    UsedSpaceMB DESC, t.Name
  • The results were identical to the ones in the report Commented Jun 7, 2017 at 14:25
  • There might be unused space as well. Try shrinking your db. Commented Jun 7, 2017 at 14:29
  • My current mdf file size is 5.22GB, the 3.98GB I mention in my question is the the file size after deducting unused space Commented Jun 7, 2017 at 14:41
  • @EduardoWada your deduction is of the unallocated space and not unused space. But you are still correct in that a shrink file wont solve the space. Anyway, Please add a screenshot of the shrink file dialog for the data file. Commented Jun 7, 2017 at 14:45
  • 1
    @DanielBjörk Added, but since someone mentioned the service broker (which is an usual feature that I'm using), my suspicions are headed towards that direction, I may have some gigantic broker related queue that doesn't get listed in the report. Commented Jun 7, 2017 at 14:55

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.