patternsqlMinor
Sum of table sizes don't match with mdf size
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:
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.
And below is the query to get the per-object details along with a grand total line at the top:
;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.