I understand that shrinking the data files is a bad idea in SQL Server due to index fragmentation.
I want to ensure that I completely understand before arguing my point.
I know that when dbcc shrinkfile (unless with truncateonly) is run, the pages from the back are moved to free spaces in an earlier position.

Does this function get the very last page in the data file and place it in the first available space?

In other words, if we have this situation:

[ Page 1 ] [ Page 2 ] [ empty1 ] [ empty2 ] [ Page 3 ] [ Page 4 ] [ empty3 ] [ empty4 ] [ Page 5 ]

...after executing a dbcc shrinkfile would [ Page 5 ] be moved to [ empty1 ] because that is the first available slot? and so on...

In the end it eventually would look like this:

[ Page 1 ] [ Page 2 ] [ Page 5 ] [ Page 4 ] [ Page 3 ]

Any help would be appreciated.


The shrink algorithm works exactly as you described it. A good description can be found in Paul Randal's blog: https://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

Why does this happen? A data file shrink operation works on a single file at a time, and uses the GAM bitmaps (see Inside The Storage Engine: GAM, SGAM, PFS and other allocation maps) to find the highest page allocated in the file. It then moves it as far towards the front of the file as it can, and so on, and so on.

In your example, Page5 ends up in Empty1.


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.