Unable to shrink data file with LOB data sql server 2017

I have a huge table that stores pdf documents using datatype “image”. 

Recently we started to offload files to blob storage, that is why we want to shrink main data file.

All my tries to shrink the file wasn’t successful. 

Usually, it stucks on 97-98% and doesn’t go any further. 

Even shrinking by small chunks 1024 mb is not working. 

What else should I try to solve this problem?

This is query I am using for shrinking:

DECLARE @i int = 4501095 -- set original size

WHILE @i >= 4481000 Begin -- set final size
DBCC SHRINKFILE (N'DataFile' , @i)
--Shrink by 1024 MB at a time. 
SET @i -= 1024
END
GO

Table description:

Table occupies 80% of the database and has:

Rows: 6,472,619 rows;

Size: 2641.5 GB;

LOB data:   2639.1 GB LOB

data files:

How long does it take to do a table scan for this table, including reading the LOB data? Multiply that with 346,161,152 and you get how long time it will take to do a full shrink, roughly. Then reduce some, based on at what point it reaches the target file size. Still a very very long time.

Say that you shrink “only” 1 GB at a time. That is 131,072 pages. Say that “only” 100,000 of those pages are LOB pages. Still a hefty number of table scans!

Think about what shrink does. It moved pages from the end of the file to the beginning of the file. For a LOB page, SQL Server need to adjust whatever points to this page to reflect the new page adress. A LOB page doesn’t have a back-pointer, so it needs to do a table scan for each page that is moved. That is a whole lot of table scans!

Of course, the shrink will stop when it gets to the target size.

There are some details buried in here, but it doesn’t change above. Data for LOB types can be in-row (when it fits), but since you say you have 2639 GB LOB data, I assume those are in fact LOB pages. A lob page can be shared between rows, reducing space usage compared to the predating design – or it can be dedicated to a row. There’s also a B-Tree structure for the LOB data (allowing “searched in the middle”) but the nodes in the tree are not necessarily pages, they are data on those pages. So, regardless of the details, it if is indeed a LOB pages that is moved, then all pages need to be read to find out which of those pages might point to this moved LOB page.

In your case, just live with the free space. It doesn’t hurt anything and it doesn’t make your backup significantly larger (since backup only backs up used extents).