1

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.

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.