Sql-server – SQL Database size incorrect and database file growing while empty

database-sizedatafilesql serversql-server-2017storage


I have a database data file showing as 200GB on disk, and 200GB allocated space in the database properties. sp_spaceused also shows 200GB allocated. All show nearly zero free space.

However, querying table and index sizes shows that there is only approx 4GB of data in the database. I believe this is correct.
I have a copy of the database that I have run UPDATEUSAGE which updated the allocated and free space as expected showing 196GB free space suddenly available. This allowed me to shrink the database down to a sensible 10GB.

However, on the live database (in a maintenance window) when I try UPDATEUSAGE, and sp_spaceused @updateusage = 'true' it does not update and show the correct available free space.
I have tried recycling the SQL Server service and again running these commands but nothing changes.

Has anyone seen problems with the allocated and available space stats, and any ideas how to resolve this?
Log tables are empty and backed up hourly, so are not contributing greatly to these figures.

The second problem is obviously to figure out why the database is growing like this. It seems SQL thinks there is no free space, so grows the file with every insert. I have read some DDL operations and heaps do not correctly update the unallocated space, so this is something to look into. I have checked the space used and available by each of the heaps but these do not seem to show excessive. I suspect is it something related to a datawarehouse ETL process. Has anyone else seen anything like this?

For now, I need to be able to shrink this database down as it is growing quickly and incorrectly, and is almost completely empty. I know about fragmentation after shrink, and I'll do that.


Running the following query to get table sizes:

    t.NAME AS TableName,
    s.Name AS SchemaName,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) 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
GROUP BY     t.Name, s.Name, p.Rows

Shows me the table sizes, including indexes. Summing this returns the approx 4GB expected. I've also checked indexed views and their size is negligible.


However sp_spaceused returns this:

database_size   unallocated space
197479.50 MB    181.38 MB

reserved        data           index_size   unused
201396352 KB    200640656 KB    534448 KB   221248 KB

This suggests to me the problem is with heaps. But I cannot account for the missing 196GB.

As suggested by SQLpro it may be internal_tables using the space. This sounded likely as we use service broker and change tracking. I also liked the suggestion because it may have explained why the TEST copy could have the allocated/unused space info updated if service broker was not running/enabled.

However, I used the following query to sum the space reserved and used but it only accounts for about 1.5GB.

SELECT SUM(ReservedMB) ReservedMB, SUM(UsedSpaceMB) UsedMB
FROM (SELECT s.name, it.name tname,
        SUM(ps.reserved_page_count) AS reserved,
        SUM(ps.used_page_count) AS used,
         CAST(ROUND(((SUM(ps.reserved_page_count) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS ReservedMB, 
     CAST(ROUND(((SUM(ps.used_page_count) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB
     FROM sys.dm_db_partition_stats ps
     INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
            JOIN sys.schemas AS s ON it.schema_id = s.schema_id
     GROUP BY it.parent_id, it.name, s.name)ds


ReservedMB  UsedMB
1318.66     1288.65


As mentioned by SQLpro, the problem did turn out to be caused by service broker. All my above previous queries of table sizes did not find the particular internal table that was the cause of the problem. However, after a bit of digging I found this query:

/* Size of all internal and normal table objects */
  SELECT name = OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id), 
       rows = SUM(CASE
                      WHEN index_id < 2
                      THEN row_count
                      ELSE 0
       reserved_mb = 8 * SUM(reserved_page_count) / 1024, 
       data_mb = 8 * SUM(CASE
                             WHEN index_id < 2
                             THEN in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count
                             ELSE lob_used_page_count + row_overflow_used_page_count
                         END) / 1024, 
       index_mb = 8 * (SUM(used_page_count) - SUM(CASE
                                                      WHEN index_id < 2
                                                      THEN in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count
                                                      ELSE lob_used_page_count + row_overflow_used_page_count
                                                  END)) / 1024, 
       unused_mb = 8 * SUM(reserved_page_count - used_page_count) / 1024
FROM sys.dm_db_partition_stats
GROUP BY object_id
ORDER BY reserved_mb DESC;

This showed the problem right away. Top record:

name                rows    reserved_mb data_mb index_mb    unused_mb
sys.sysxmitqueue    264598  198097      198082  3           11

Thank you to everyone who helped. I also learnt about the problem with heaps which was new to me.

Best Answer

I have encountered a similar problem, when using Service Broker that uses internal tables not seens in many query that you do to calculate the amount of data. Use :

SELECT @SQL = @SQL + N'EXEC sp_spaceused ''' + s.name + '.' + it.name + ''';'
FROM   sys.internal_tables AS it
       JOIN sys.schemas AS s
          ON it.schema_id = s.schema_id; 

To see those volumes