Sql-server – Index Maintenance – Reorganize After BIG SQL Server Shrink

fragmentationola-hallengrenshrinksql server

I'm planning to execute the below index maintenance created by Ola Hallengren in a 1TB Database.

EXECUTE [dbo].[IndexOptimize]
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE',
@FragmentationHigh = 'INDEX_REORGANIZE',
@FragmentationLevel1 = 50,
@FragmentationLevel2  = 80,
@UpdateStatistics = 'ALL'
,@FillFactor = 100

So basically I know that shrinking is a very bad idea and not a common practice,
I'm Shrinking because is really needed and it has been working good.

So due to the fragmentation that the shrinking is causing, I'm planning to use the mentioned stored procedure to Reorganize all the affected indexes and avoid getting the freed space back with a Rebuild

So the specific question would be, does this look like a good approach or have you faced something similar and used another approach for it ?, or if you have suggestions are gonna be highly appreciated.

Best Answer

If you have to shrink your DB ola's script for reorganizing is probably the best approach for handling the fragmentation.

please note, this is a costly operation- plan it to run on maintenance hours if possible and make sure that if you're in a full recovery model you have enough space on your disk that holds the log file and that you're backing up transaction log files properly.

Related Question