SQL Database size incorrect and database file growing while empty

Question

RESOLVED - SOLUTION AT BOTTOM OF POST

PROBLEM 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.

Thanks.

EDIT Running the following query to get table sizes:

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows,
    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.

TotalSpaceMB
3775.16

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.

EDIT 2 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,
        it.parent_id,
        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

results

ReservedMB  UsedMB
1318.66     1288.65

SOLUTION

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
                  END), 
       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.

Answer 1

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 :

DECLARE @SQL NVARCHAR(max) = N'';
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; 
EXEC (@SQL);

To see those volumes

Answer 2

Each allocation has a linked list of IAM pages. If we ignore partitioning and LOBs and such to keep the discussion straight forward, you then can imagine each index having a series of IAM; and the actual data (be it clustered index or heap) also have a series of IAMs.

An IAM maps up 2GB in the database file, reflecting which extents are owned by this allocation (heap/index). If the allocation uses space outside these 2GB, when it has one more IAM (per 2GB it uses space from in the database file) - the IAMs are connected as a linked list.

Queries that tell you how large a table is or how full a database is don't go by these IAMs, that would take too long time. They use system tables (exposed through views) which has stats about space usage. These can be off, as you know. But them being off has no bearing on when space is allocated. When SQL Server need to find free space, it uses pages such as GAMs, SGAMs and IAMs (and, possibly PFS). So if you have a heap which according to the IAMs (and indirectly the SGAM) owns lots and lots of extents (even though there isn't any data there), then these extents can't be used by something else.

Doing UPDATEUSAGE won't help, since that command will just read these structures (perhaps only IAMs) to reflect in the system tables how large an allocation is. I.e., you exectuing UPDATEUSAGE wasn't what allowed you to shrink, the shrink would have worked just as fine without the UPDATEUSAGE.

So, your heaps are apparently (if my reasoning is sound) your problem. What to do? Don't have heaps or rebuild them. But if you keep your heaps, the issue will come back.