1

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.

  • 1
    Do you have tables that are heaps? Have you tried rebuilding them (or adding clustered indexes, like you should have already)? I think there are cases where there isn't a straightforward way to see that that's where your allocations are being used up, but heaps are notorious for holding onto every byte they ever used even if you delete / truncate / defrag / etc. – Aaron Bertrand Sep 16 at 2:19
  • Thank you very much for the reply Aaron. Yes, unfortunately the customer has created many heaps as part of a custom data warehouse / ETL solution. When I queried sp_spaceused on these heaps, they appeared normal and didn't appear affected by the unallocated space problem. However, I do still suspect these are to blame. Overnight the customer has added clustered indexes to many of his heaps. I will try rebuilding the remaining heaps and try to recalculate the allocated space figures in the next maintenance window. – Chris Williams Sep 16 at 9:44
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

| improve this answer | |
  • Thank you, this sounded very possible. We use service broker and change tracking. Unfortunately we have 2500 tables and sp_spaceused run like this crashes SSMS with too many resultsets. However if I query internal_tables joined to partition_stats with CAST(ROUND(((SUM(ps.reserved_page_count) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS ReservedMB I get about 2 GB for all internal tables total. I checked this by sp_spaceused onthe largest internal table (change tracking) which accounts for about 1GB. All other tables are tiny in comparison. – Chris Williams Sep 16 at 12:21
  • The problem turned out to be caused by service broker as you suggested. The table sys.sysxmitqueue was approx 200GB in size. All my queries on sys.tables and sys.internaltables didn't find this, but a query on partition_stats with OBJECT_NAME found it. I'll update the post with the query I used to find it. Thanks for your help. – Chris Williams Sep 17 at 9:17
  • @ChrisWilliams You should still fix the heaps, though, IMHO. :-) – Aaron Bertrand Sep 18 at 5:11
  • @AaronBertrand. Absolutely. Customer has added clustered indexes to their warehouse heaps now. I'd never come across the problem with heaps before so learnt something new too, thank you. Also, it was a nice surprise to be answered by a true SQL legend. Thanks. – Chris Williams Sep 20 at 14:35
  • @ChrisWilliams LOL I think you have me confused with someone else. :-) – Aaron Bertrand Sep 20 at 14:37
0

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.

| improve this answer | |
  • Thank you for the reply Tibor. This is very useful information. The customer has added clustered indexes to most of their heaps overnight so I will try rebuilding the remaining heaps and see if that helps update the allocated/unused space. I am still unsure why UPDATEUSAGE on a test copy of the database fixed the space used/available data but not in production. – Chris Williams Sep 16 at 10:00
  • Note - when I attempted to shrink the database using SHRINKFILE without the allocated/unused space being updated, it seemed unable to recognize there was free space and was taking a very long time. However, after updating allocated/unused in TEST I could shrink by 10GB increments in seconds. – Chris Williams Sep 16 at 10:01
  • Did was TEST a binary copy of the prod database? I.e., backup/restore or detach/attach. And, of course, no work whatsoever done on either databases since that copy was produced. If not, then all bets are off and you shouldn't try to deduct anything on prod from what you see on test. Anyhow, I have a feeling that getting rid of most heaps and then rebuilding the rest as needed will sort you out! :-) – Tibor Karaszi Sep 17 at 7:15
  • I believe you are correct and as soon as I can get another maintenance window I will rebuild the remaining heaps and update here with the results. I assume the copy was produced from a restore from backup as this is how most of our customers create their test systems. Thanks again for your input. – Chris Williams Sep 17 at 8:32

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.