In the SHRINKFILE command, SQL Server isn’t especially careful about where it puts the pages being moved from the end of the file to open pages towards the beginning of the file.
- The data becomes fragmented, potentially up to 100% fragmentation, this is a performance killer for your database;
- The operation is slow – all pointers to / from the page / rows being moved have to be fixed up, and the SHRINKFILE operation is single-threaded, so it can be really slow (the single-threaded nature of SHRINKFILE is not going to change any time soon)
Recommendations:
- Shrink the file by using Truncate Only: First it removes the inactive part of the log and then perform shrink operation
- Rebuild / Reorganize the indexes once the shrink is done so the Fragmentation level is decreased
No comments:
Post a Comment