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.
Best Answer
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.