HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Sum of table sizes don't match with mdf size

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
mdfwithsizematchsizessumtabledon

Problem

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:

Solution

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;

Code Snippets

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

Context

StackExchange Database Administrators Q#175649, answer score: 6

Revisions (0)

No revisions yet.