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. This sounded likely as we use service broker and change tracking. But I cannot account for the missing 196GB.Īs suggested by SQLpro it may be internal_tables using the space. This suggests to me the problem is with heaps. However sp_spaceused returns this: database_size unallocated spaceĢ01396352 KB 200640656 KB 534448 KB 221248 KB I've also checked indexed views and their size is negligible. Summing this returns the approx 4GB expected. Shows me the table sizes, including indexes. LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,ĬAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB Running the following query to get table sizes: SELECTĬAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,ĬAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, I know about fragmentation after shrink, and I'll do that. Has anyone else seen anything like this?įor now, I need to be able to shrink this database down as it is growing quickly and incorrectly, and is almost completely empty. I suspect is it something related to a datawarehouse ETL process. I have checked the space used and available by each of the heaps but these do not seem to show excessive. I have read some DDL operations and heaps do not correctly update the unallocated space, so this is something to look into. It seems SQL thinks there is no free space, so grows the file with every insert. The second problem is obviously to figure out why the database is growing like this. Log tables are empty and backed up hourly, so are not contributing greatly to these figures. Has anyone seen problems with the allocated and available space stats, and any ideas how to resolve this? I have tried recycling the SQL Server service and again running these commands but nothing changes. However, on the live database (in a maintenance window) when I try UPDATEUSAGE, and sp_spaceused = 'true' it does not update and show the correct available free space. This allowed me to shrink the database down to a sensible 10GB. 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. However, querying table and index sizes shows that there is only approx 4GB of data in the database. I have a database data file showing as 200GB on disk, and 200GB allocated space in the database properties.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |