In this post I'll cover that problem using "Spatial Fragmentation Report"
At first, will identify the problem:
As you can see, my database is 10Gb and only 1.6 Gb are in use.
Will try to fix that problem the conventional way by trying to shrink database to 20% of it's current size:
As the result, got following message:
What is that??? I have 85% of free space in my File 1 !!! What is the problem?
Will try to target an individual file and reduce its size to ~2Gb:
After waiting for almost 2 minutes I've got following error:
Then I've tried to reduce file size for anything I can:
Then almost immediately got following result:
After checking the size again see very strange behavior: size of the file have not reduced, but amount of USED space reduced by 300Mb:
Have you ever been in the same situation before when no matter what, but you can't shrink your database?
So, now the time to see WHY SQL Server can't shrink our file.
I use following script to see object allocations/fragmentation in a data file:
Here is the result for my database:
If we zoom at the very tail we can figure out the names of tables at the very end of the file, which prevent file from shrinking:
After I performed "Index Rebuild" for all user tables the Page Allocation picture changed to this:
However, will try to shrink the file again:
This time I was more lucky sis not get an error, but also had my file shrank:
Here is how fragmentation looked after the Shrink:
The main conclusion of that exercise is that if you struggle with shrinking your database, look at your index fragmentation, that can cause errors and inability to shrink files.