January 29, 2019 at 7:29 am
I've got some dbs here that seem to be taking up more space than they should. In one inistance if I run the Disk Usage by Tables report and total up the Reserved space I get about 510MB (Data= 268MB, Index = 166MB, Unused = 76MB.) If I run the Disk Usage report it shows about 2.37GB Space Used. If I copy all the data, keys, and indexes from this db into a new db I end up with Space used of around 510MB in the new db. What could be taking up the additional space in the original db? I've already rebuilt the indexes, updated statistics, etc. Shrinking doesn't do anything either as it thinks 2.37GB is being used. Any ideas?
January 29, 2019 at 7:55 am
jholland-636029 - Tuesday, January 29, 2019 7:29 AMI've got some dbs here that seem to be taking up more space than they should. In one inistance if I run the Disk Usage by Tables report and total up the Reserved space I get about 510MB (Data= 268MB, Index = 166MB, Unused = 76MB.) If I run the Disk Usage report it shows about 2.37GB Space Used. If I copy all the data, keys, and indexes from this db into a new db I end up with Space used of around 510MB in the new db. What could be taking up the additional space in the original db? I've already rebuilt the indexes, updated statistics, etc. Shrinking doesn't do anything either as it thinks 2.37GB is being used. Any ideas?
On disk usage report, how much space is unallocated and How big is the transaction log?
Sue
January 29, 2019 at 8:37 am
The database is set to simple recovery model so transaction log size is minimal. Also the database was set to an initial size of 3GB. The actual space used is supposedly 2.37GB. 19.8% unallocated.
January 29, 2019 at 11:45 am
I've deleted every single row from every table in the database. I then ran the rebuild statement on every table to reclaim used space and also rebuilt all indexes and updated statistics. This is now a completely empty db as far as I can see but it is still showing as almost 2GB in size. If I do a backup I get an almost 2GB file. I've tried to research things like full text indexing, query stores, etc. none of which should be turned on, but I'm grasping at straws here. What could possibly be using all of this space in my now empty db?
January 29, 2019 at 12:12 pm
jholland-636029 - Tuesday, January 29, 2019 11:45 AMI've deleted every single row from every table in the database. I then ran the rebuild statement on every table to reclaim used space and also rebuilt all indexes and updated statistics. This is now a completely empty db as far as I can see but it is still showing as almost 2GB in size. If I do a backup I get an almost 2GB file. I've tried to research things like full text indexing, query stores, etc. none of which should be turned on, but I'm grasping at straws here. What could possibly be using all of this space in my now empty db?
Try executing the following in the database and post back the results: SELECT
DB_NAME() AS DBName,
[name] AS [FileName],
[Type_Desc] as FileType,
CAST(size/128.0 as DECIMAL(10,2)) FileSizeMB,
CAST((CAST(size/128.0 as DECIMAL(10,2)) -
CAST(FILEPROPERTY(name,'SpaceUsed')AS INT)/128.0) as DECIMAL(10,2)) AS FreeSpaceMB,
CAST(CAST(FILEPROPERTY(name,'SpaceUsed')AS INT)/128.0 as DECIMAL(10,2)) as UsedSpaceMB
FROM sys.database_files
WHERE [type] in (0,1)
Sue
January 29, 2019 at 12:23 pm
DBName FileName FileType FileSizeMB FreeSpaceMB UsedSpaceMB
church Church ROWS 2951.31 1014.62 1936.69
church Church_log LOG 15.69 11.12 4.57
January 31, 2019 at 11:36 am
jholland-636029 - Tuesday, January 29, 2019 12:23 PMDBName FileName FileType FileSizeMB FreeSpaceMB UsedSpaceMB
church Church ROWS 2951.31 1014.62 1936.69
church Church_log LOG 15.69 11.12 4.57
Not sure how you defined things for the tables or what is enabled for the database. You could try finding which empty tables are taking up the space and check the definitions of those tables. A quick and dirty way to find the space used by each table, you could just do something like: sp_msforeachtable N'EXEC sp_spaceused [?]';
Sue
February 1, 2019 at 9:25 am
There's seriously less than 6MB being reserved by the tables in the db now. No rows in any of them. It's just not making sense.
February 1, 2019 at 9:27 am
Here is the Disk Usage report after shrinking the files as much as SQL Server would allow. 1.91GB and nothing in it.
February 1, 2019 at 7:32 pm
jholland-636029 - Friday, February 1, 2019 9:27 AMHere is the Disk Usage report after shrinking the files as much as SQL Server would allow. 1.91GB and nothing in it.
A database doesn't ever have nothing in it even if you delete all the data. 🙂
Check by allocation units - it might turn up something: SELECT
OBJECT_NAME(p.[object_id]),
SUM(au.total_pages) as TotalPages,
SUM(au.used_pages) as UsedPages,
SUM(au.data_pages) as DataPages,
AVG(p.rows) as [PartitionRows]
FROM sys.allocation_units au
INNER JOIN sys.partitions p
ON au.container_id =
CASE WHEN au.[type] in(1,3) THEN p.hobt_id
ELSE p.[partition_id]
END
GROUP BY OBJECT_NAME(p.[object_id])
ORDER BY TotalPages desc
Sue
February 1, 2019 at 8:27 pm
Hmmm, a lot of change tracking data. Retention period is set for 2 days...
February 2, 2019 at 2:04 pm
jholland-636029 - Friday, February 1, 2019 8:27 PMHmmm, a lot of change tracking data. Retention period is set for 2 days...
Yes it's the change tracking. And it looks like it's enabled on most/all tables - 80 - 100 or something, didn't count them all.
The retention period isn't exact and from what I remember when testing it the retention period is more like a minimum retention period. And there are times the automatic cleanup doesn't clean up enough. Microsoft did add a new stored procedure for manual cleanups - sp_flush_CT_internal_table_on_demand
This MS blog post goes over the issues -
Change Tracking (aka Syscommittab) Issues and Cleanup – Part 1
Sue
February 28, 2019 at 2:49 pm
Just wanted to circle back here and thank you for the assistance on this. I was finally able to get back on this project yesterday and found that CU's 1, 2, 8, and 10 for SQL Server 2017 all had improvements for cleaning up change tracking data. I installed CU13 at a couple of sites yesterday and saw that space was freed up dramatically this morning. One site went from almost 6GB to 900MB. Huge improvement! Thanks again.
February 28, 2019 at 3:00 pm
jholland-636029 - Thursday, February 28, 2019 2:49 PMJust wanted to circle back here and thank you for the assistance on this. I was finally able to get back on this project yesterday and found that CU's 1, 2, 8, and 10 for SQL Server 2017 all had improvements for cleaning up change tracking data. I installed CU13 at a couple of sites yesterday and saw that space was freed up dramatically this morning. One site went from almost 6GB to 900MB. Huge improvement! Thanks again.
You are very welcome - thanks a lot for following up!
It's a bit of an ugly issue when you hit it (felt that pain before). Good to know they are finally getting things fixed in the CUs. What a freaking change in size!
Sue
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply