Well as I learnt in previous blog Shrink Database caused fragmentation, but why? Before we discuss fragmentation, let me try to explain DBCC ShrinkDatabase command.
DBCC ShrinkDatabase command takes database name, Target Percent and Truncate | NoTruncate as parameters.
Truncate or NoTruncate indicate to database engine if space reclaimed by database engine be given back to Operating System or not.
Truncate: SQL Database engine releases space to OS, NOTruncate: Does not release space to OS though file content is (leaving empty space at the end).
Target Percent is slightly confused here. Target Percent is not Target Size that database should be post shrinking operation but Target Percent is Target free space database should have post shrinking.
When database is shrunk either by Management Studio or DBCC command it moves data from end of file to beginning or starting of file. It tries to fill pages as it gets to move from end to start of file. So pages can not be moved in chunks (as there may not be huge chunks of pages at start in a normal production environment) but pages maybe moved individually inducing fragmentation.
In this case I have learnt that a completely defragmented index is fragmented to 97% post shrink operation and that is terrifying with regards to performance. Are you all seeing the same 🙂
Thus if one if forced to do a shrink operation it should be followed by a rebuilding Index (DBCC DBREINDEX) to defragment index.
Also this is one of reasons for Auto Shrink Database Property to be turned off.
Now in a later post I would like to check if Compressing and Decompressing an existing table will induce fragmentation or not or maybe something more interesting 🙂
If you have any questions / comments / corrections pertaining to any of fragmentation details please feel free to send mail to me at firstname.lastname@example.org