Almost Empty Data File is still taking time to Shrink in SQL Server

Question

I had one file in a filegroup FG1 which was orginally of size 130 GB and almost full. It was having partitioned tables in them. I have moved all the big tables to other filegroup FG2 and now FG1 is having 93.5% internal free space.

Purpose of doing all this was based on below theory:

"when filegroup FG1 was almost full, shrinking was taking too much time. So after moving almost all the data to other filegroup FG2, shrinking of FG1 should be faster."

When I tried to shrink almost entire FG1 with below command it still took 1 Hr 56 Mins to shrink.

DBCC SHRINKFILE('FG1',10240)

Am I missing something that would have made this shrinking more faster? or it is expected to still take it this much of time?

NOTE: I tried DBCC SHRINKFILE(FILEID,TRUNCATEONLY) after moving the data, but unforuntately it didn't help.

Answer 1

Three things that can make shrink of data file very slow are:

Heaps tables. For every page moved, SQL server has to adjust every index, for all rows.

LOB pages. For every page moved, SQL Server has to do a table scan of the table (there's no back-pointer).

Shrink being blocked - it has an endless timeout.

My guess is that you encountered one or both of the first two.