0

I am developing a plan for SQL Server 2012 EE to properly downsize and type (nchar to char types) some needlessly Unicode nvarchar(max) fields and would like to optimize the database size as part of the downtime by doing a one time shrink. Experiments have shown a 50% allocated space savings which is 11G of data.

After reading and experimenting, it's evident that shrinking a database causes index fragmentation and rebuilding indexes causes the database to expand. A real Catch-22 situation. I don't want to leave 50% free space in the database which is 11G of disk storage in this case.

Would the following be a decent approach for a one time shrink that would allow ending up with non-fragged indexes and up to date index statistics?

o Backup w/ verification and duplicate backups.

o Drop all indexes.

o Rebuild any too fat tables via copy into new tables, then drop and rename tables. This is currently working well.

o Shrink the database leaving a reasonable amount of free space.

o Recreate all indexes that were dropped.

o Validate the database and check fragmentation.

Pointing out any caveats, suggestions, gotchas or alternatives to consider much appreciated.

Thx, Dave

Improve this question
0

1 Answer 1

Reset to default
3

Simple recovery model doesn't "turn off the log" - so generally, unless you're going to break any of these activities into very small chunks, just pre-size the log large enough to deal with the largest rebuilds, and don't bother messing with the recovery model.

Also, and again generally, unless your data will never grow again, freeing up the space temporarily is of little benefit (given that I don't know what you mean by "reasonable"). You're going to need to leave free space on the drive, in case the database grows again, right? Why shrink it only to have it grow again? Are you going to provide short-term rental on that space until the database grows? Are you just trying to avoid free space % alerts?

Suggestion: give these posts a good read...

Improve this answer
6
  • 1
    Good points. I've modified the question accordingly. I guess my main concern is leaving 50% free which would take towards a year to fill back up, but perhaps I should consider just leaving it there, in which case will mark this as the answer. Thx.
    – Dave
    Commented Oct 15, 2015 at 18:01
  • One year is practically no time. I routinely plan to leave a year's worth of room in my databases just in case.
    – Hannah Vernon ♦
    Commented Oct 15, 2015 at 18:20
  • Agreed. And in my experience one years expected growth general is gone within 6 months.
    – Kenneth Fisher
    Commented Oct 15, 2015 at 18:26
  • I was hoping I had found a reasonable way to have my cake and eat it tool. Thx folks. I'm not going to worry about the free space and just enjoy it. I've indeed got better things to do. I had read almost all those articles except one so thx again.
    – Dave
    Commented Oct 15, 2015 at 18:39
  • Actually there is a big difference with Simple recovery model which does sort of turn off the log in one respect. Full recovery model uses a lot more transaction log since I am not backing it up in between the modifications I am making to the database. In simple mode, it reaches a high water mark much lower than the accumulative size reached in Full recovery model and only slightly larger than when it started likely due to the 70% auto checkpoints. Only used 11G vs. running out of space and failing at 27G as the disk ran out of space. Not sure how much more it would have actually taken.
    – Dave
    Commented Oct 16, 2015 at 1:25

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.