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.

  • 1
    Why do you need to shrink? Is disk space an issue? Also - what's causing the DB to grow that much that you need to shrink? If there's somedata processing going on that makes the DB grow, then I'd look at making sure you have enough disk space for the DB to grow, then just leave it, without shrinking. You can still run the index reorg/rebuild as part of your maintenance scripts – Lee Walters Aug 22 '18 at 5:27
  • @LeeWalters shrink is done, and yes, due to disk issues, not possible to add drives, that's why, in addition, db is populated with blob data, so the question is more from the index fragmentation side. – giantLincecum Aug 22 '18 at 5:35
  • before i answer your question is the database on simple recovery or full? – sql_girl Aug 22 '18 at 9:50
  • If you are routinely shrinking the database to conserve drive space and adding more drives isn't possible, you have a much bigger over all issue. Shrinking the database is a band-aid solution at best. Can you store the BLOB data outside of the database and just pointers to the location? Rebuilding indexes returns drive space, reorganizing does not. Have you looked at row or page compression? thomaslarock.com/2018/01/… – SQLMac Aug 22 '18 at 13:14
  • @SQLMac yup, that's a fact, rebuild can return space that was released, actually there are no more shrink to do, but the plan is to reorganize as shown in the stored procedure in order to avoid what we mentioned earlier with the rebuild. – giantLincecum Aug 22 '18 at 18:06

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.

  • Indexes will have > 85% of fragmentation for sure, so rebuild does not look like a good option because will take all the cleared space back in the database, that's pretty much why I'm using reogranize in the sotred procedure options. – giantLincecum Aug 22 '18 at 18:10
  • you're right, i changed my answer. – sql_girl Aug 22 '18 at 18:55
  • 1
    Gotcha, yup would be in a non-business hour and to keep the tlog file as large as possible is something already considered :) – giantLincecum Aug 22 '18 at 21:56

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.