0

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.

8
  • 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 Aug 22, 2018 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.
    – user141153
    Aug 22, 2018 at 5:35
  • before i answer your question is the database on simple recovery or full?
    – sql_girl
    Aug 22, 2018 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, 2018 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.
    – user141153
    Aug 22, 2018 at 18:06

1 Answer 1

0

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.

3
  • 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.
    – user141153
    Aug 22, 2018 at 18:10
  • you're right, i changed my answer.
    – sql_girl
    Aug 22, 2018 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 :)
    – user141153
    Aug 22, 2018 at 21:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.