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
Best Answer
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...