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