Shrink database or DB files not reducing size even after dropping biggest table

Question

Our MDF file was originally 550GB in size (log is just 10MB), and the biggest table we had [HourlyCounters] was 140 million rows with 580 columns (varchar[50]). It's a table with a very inefficient schema, but there's nothing we can do about that.

Anyways, after moving some data around, I was able to drop [HourlyCounters]. So now, the new table (with the same name) has 50K rows. I then ran SHRINKDATABASE and SHRINKFILE, and the MDF went down about 40GB. I know that's a decent chunk, but I know for a fact that this dropped table was larger than that (the current backup of that table with only 7M rows is 63GB).

How can SQL Server release all that free space? The new version of [HourlyCounters] doesn't currently have any indexes, but I know the old one did have them.

It's worth mentioning how I went about dropping the table:

• Rename the original table [HourlyCounters] to [HourlyCounters_Old] with a right-click in SSMS and Rename. This table had indexes.
• Create a new table [HourlyCounters] with no indexes so that processes that stored in this table are not affected.
• Create table [HourlyCounters_New] where I would store the most recent data from [HourlyCounters] (now called [HourlyCounters_Old]). [HourlyCounters_New] now has 7M rows.
• Drop [HourlyCounters_Old]

Other info that may help: The drive where the data files are stored (E:) is 1.89TB, with 1.44TB used and 468GB free.

This is the result of the following query:

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 
AS AvailableSpaceInMB
FROM sys.database_files;   

name      AvailableSpaceInMB
DB        53490.812500
DB_log    441.703125

Thanks.

Answer 1

Exactly how did you run the SHRINK commands? My understanding is that by default it just releases unused space at the end of the file, but you can also have the database compact itself first to make more space available. This is a slow and expensive operation, though; around an hour per 10 GB in my recent experience.

That said, I suspect your database is using more space than it ought to due to fragmentation. If you run a query like this:

SELECT TOP 10
    '[' + S.name + '].[' + O.name + ']' AS TableName,
    CASE WHEN I.type_desc = 'HEAP' THEN '(heap)' ELSE '[' + I.name + ']' END AS IndexName,
    Stats.avg_fragmentation_in_percent, Stats.fragment_count
FROM
    sys.indexes AS I
    INNER JOIN sys.objects AS O ON I.object_id = O.object_id
    INNER JOIN sys.schemas AS S ON O.schema_id = S.schema_id
    CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), O.object_id, I.index_id, NULL, NULL) AS Stats
WHERE
    S.name NOT IN ('sys')
ORDER BY
    Stats.avg_fragmentation_in_percent * Stats.fragment_count DESC

...you'll see the tables and indices which are both large and badly fragmented. Running ALTER INDEX MyIndx ON MyTBl REBUILD on a few of those should recover some space. As Aaron Bertrand said, you probably don't want to actually free this space with SHRINK; your database will need it sooner or later.

Heap tables are prone to wasted space; using ALTER TABLE MyTbl REBUILD on your bigger tables may help too.