Shrinking database File with a help of "Spatial Fragmentation Report"

There are more than "many" questions in the internet from people who can't claim free unused space from their data files.

In this post I'll cover that problem using "Spatial Fragmentation Report"

At first, will identify the problem:

USE [TestFF];
GO
SELECT Name, physical_name,
    size/128. as "File Size, Mb",
    FILEPROPERTY(name, 'SpaceUsed')/128. as "Space Used, Mb"
FROM sys.database_files
WHERE file_id = 1;

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:

DBCC SHRINKDATABASE('TestFF',20);

As the result, got following message:

DBCC SHRINKDATABASE: File ID 1 of database ID 5 was skipped because the file does not have enough free space to reclaim.
DBCC SHRINKDATABASE: File ID 2 of database ID 5 was skipped because the file does not have enough free space to reclaim.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

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:

DBCC SHRINKFILE('TestFF',2000);

After waiting for almost 2 minutes I've got following error:

Msg 0, Level 11, State 0, Line 144
A severe error occurred on the current command.  The results, if any, should be discarded.

Then I've tried to reduce file size for anything I can:

DBCC SHRINKFILE('TestFF');

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:

IF Object_ID('tempdb..#Used_Pages_List') Is not Null
 DROP TABLE #Used_Pages_List;
GO
SELECT object_id, allocated_page_page_id as page_id INTO #Used_Pages_List
FROM sys.dm_db_database_page_allocations(DB_ID(), NULL, NULL, NULL, NULL) as a
WHERE partition_id = 1
GO
CREATE CLUSTERED INDEX #CLIX_Used_Pages_List ON #Used_Pages_List(object_id,page_id)
GO
DECLARE @i INT = 0;
DECLARE @m INT = (SELECT MAX(object_id) FROM #Used_Pages_List);
DECLARE @g TABLE(Alloc GEOMETRY, Table_Object SYSNAME, object_id INT);
DECLARE @s INT = 131072
DECLARE @LineMegabytes INT = (SELECT Size/(64000)+1 FROM sys.database_files WHERE file_id = 1);
DECLARE @d INT = 128*@LineMegabytes;

DECLARE @DBSize INT = (SELECT Size FROM sys.database_files WHERE file_id = 1) / @d + 1
DECLARE @t TABLE(ID INT, MessageText VARCHAR(100));

INSERT INTO @t(ID,MessageText) VALUES
(1,'Scale: 1 Square 10x10 = 1 Page (8 Kb)'),
(2,'Row: ' + CAST(@LineMegabytes as VARCHAR) + ' Mb'),
(3,'Vertical: 100 points = ' + CAST(10*@LineMegabytes as VARCHAR) + ' Mb')

SELECT * FROM @t ORDER BY ID;

WHILE @i < @m
BEGIN
 SELECT @i = MIN(object_id) FROM #Used_Pages_List
 WHERE @i < object_id

 PRINT CONVERT(VARCHAR,@i) 

 INSERT INTO @g(object_id, Table_Object, Alloc) 
 SELECT @i, '[' + Object_Schema_Name(@i)+ '].[' + Object_Name(@i) + ']'
  , CONVERT(GEOMETRY,'POLYGON(' + SUBSTRING( (
  SELECT 
   ',(' 
   + CONVERT(VARCHAR, (page_id % @d) * 10) + ' ' + CONVERT(VARCHAR, (page_id / -@d) * 10 ) + ','
   + CONVERT(VARCHAR, (page_id % @d+1) * 10) + ' ' + CONVERT(VARCHAR, (page_id / -@d) * 10 ) + ','
   + CONVERT(VARCHAR, (page_id % @d+1) * 10) + ' ' + CONVERT(VARCHAR, (page_id / -@d) * 10 - 10) + ','
   + CONVERT(VARCHAR, (page_id % @d) * 10) + ' ' + CONVERT(VARCHAR, (page_id / -@d) * 10 - 10) + ','
   + CONVERT(VARCHAR, (page_id % @d) * 10) + ' ' + CONVERT(VARCHAR, (page_id / -@d) * 10 ) + ')'
  FROM #Used_Pages_List
  WHERE @i = object_id
  FOR XML PATH ('')
 ),2,@@TEXTSIZE) + ')');
END

SELECT object_id, Table_Object, Alloc FROM @g
UNION ALL
SELECT 0, 'Database Size', CONVERT(GEOMETRY,'LINESTRING(0 ' 
 + CONVERT(VARCHAR, @DBSize * -10) + ', ' + CONVERT(VARCHAR, @d * 10) + ' ' + CONVERT(VARCHAR, @DBSize * -10) + ')')
ORDER BY object_id
GO

Here is the result for my database:

As you can see, I definitely have a lot of free space, but my data are so spread across the file and especially up to it's border, that there is no way to make file size smaller.

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:

As you can see, the most of the tables were moved to the beginning of the database, but some are still wondering somewhere in the middle. Lets Zoom and figure out which tables might prevent Shrinking of the file:

Unfortunately, it is the system table and we can do nothing about it.

However, will try to shrink the file again:

DBCC SHRINKFILE('TestFF',2000);

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.