3

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

  • Is Service Broker enabled for this database? – sepupic Jun 7 '17 at 13:40
  • Great question, do you have change tracking enable on the database? – Daniel Björk Jun 7 '17 at 14:06
  • 1
    Yes, service broker is enabled – Eduardo Wada Jun 7 '17 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 – Eduardo Wada Jun 7 '17 at 14:10
  • How does it look like in the Shrink File (File and not database) dialog. Can you post a screenshot of that? – Daniel Björk Jun 7 '17 at 14:30
6

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 – Eduardo Wada Jun 7 '17 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 :-) – Solomon Rutzky Jun 7 '17 at 15:38
0

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
  • The results were identical to the ones in the report – Eduardo Wada Jun 7 '17 at 14:25
  • There might be unused space as well. Try shrinking your db. – Olivier Jacot-Descombes Jun 7 '17 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 – Eduardo Wada Jun 7 '17 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. – Daniel Björk Jun 7 '17 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. – Eduardo Wada Jun 7 '17 at 14:55
0

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, privacy policy and cookie policy

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