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.