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.

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

1 Answer 1

Reset to default
1

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.

Improve this answer
0

Your Answer

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