patternMinor
Database Shrinking as a logged operation
Viewed 0 times
databaseloggedshrinkingoperation
Problem
I would like to know if shrinking a data file is a logged operation or not. I do not have a definite answer to this. I have heard people say it is a logged operation but so far I did not see an evidence of this.
Solution
I would like to know if shrinking a data file is a logged operation or not.
It is fully logged operation and is severely bad process so ALWAYS try to avoid it. What it does is, when you hit the shrink button, quoting from Paul Randal blog
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 the case above, it completely reversed the
order of the clustered index, taking it from perfectly defragmented to
perfectly fragmented.
So basically it does page movement and frees empty space from the pages. This movement of page is logged in transaction log file. Due to this movement the logical ordering of pages changes and hence logical fragmentation comes in. The point to note here is this is not an atomic operation. If you stop the shrinking operation in between the changes done so far is not lost it is maintained and when you start next time it will continue from where it left.
See this Blog from Paul Randal for data file size management if you are worried about growing data file size.
It is fully logged operation and is severely bad process so ALWAYS try to avoid it. What it does is, when you hit the shrink button, quoting from Paul Randal blog
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 the case above, it completely reversed the
order of the clustered index, taking it from perfectly defragmented to
perfectly fragmented.
So basically it does page movement and frees empty space from the pages. This movement of page is logged in transaction log file. Due to this movement the logical ordering of pages changes and hence logical fragmentation comes in. The point to note here is this is not an atomic operation. If you stop the shrinking operation in between the changes done so far is not lost it is maintained and when you start next time it will continue from where it left.
See this Blog from Paul Randal for data file size management if you are worried about growing data file size.
Context
StackExchange Database Administrators Q#144256, answer score: 4
Revisions (0)
No revisions yet.